User Profile
heylookitsme
Brass Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Power Query Find Text and Delete Anything After
Excel Power Query what would be the M Code for finding the String "NUL" and then remove any Text after the "NUL" String. I have several serial numbers for example like 1287JLPO-NUL-00-000-00000 HLQA-00-NUL-000-0000 I just need to delete any text after NUL. As you can see, I can't just replace - or 0 because those could be found prior to NUL. Thanks in advanced for your help and I will be sure to mark your answer when complete.Solved904Views0likes4CommentsRe: Slow Excel Sheet - Even after everything's deleted
Solved my problem. I'll post in case it helps another user out there. Check to ensure you do not have too many objects, comments or shapes in your sheets. This can be caused by copy and paste procedures inadvertently pasting them many times over without you realizing it. Simply, select the Home option in Excel ribbon and use the drop down under Find and Select. Select selection panel and you will see to the right if you have a lot of items showing that need to be deleted.2.1KViews0likes1CommentSlow Excel Sheet - Even after everything's deleted
Okay, this one blows my mind. I have a sheet with a table that acts like it contains volatile formulas. It's very laggy, filtering is super slow, manual inputs are slow. Even after I have deleted everything on the sheet!!! All the things I tried below are after I deleted all data in the sheet. File size is only 8MB. First thing I checked was VBA and it does have a macro that runs a worksheet change event. This was all commented out and still runs like this. I checked Formula Names to see if there were any external connected workbooks, and there was not. I checked Queries & Connections and there are none. One strange thing I noticed is when I select any other sheets in the workbook it selects the sheet instantly and I can work quickly or as normal in the sheet without any hesitation or lag. When I go back to the problem sheet and click on it, it is slow to react. Any cell within that sheet that I select (non edit mode or edit mode) it lags behind about 2-3 seconds before selecting it. I do not have any other workbooks open and little to no other programs open. I have a laptop with high specs that could run circles around most laptops. I don't know what else to check that would potentially fix this issue. Re-building this workbook will take way too long, potentially days. Oh, I use MS 365 Enterprise 4 with the latest updates and I am on Current Channel using Win 10.Solved1.9KViews0likes2CommentsExcel Memory & Performance Limitation
My previous laptop (which had some great specs) I noticed that when Excel Memory Usage rose above 1,800 MB that it would start behaving badly and do really random weird stuff (Example: Randomly freeze for short periods of time, click in a cell and it does not recognize that you clicked on it, but the data is in the formula bar, and you can change it. These are just two off the top of my head, there are many other random things as well). Now I have a new laptop with amazing specs, and I am using Microsoft Office 365 Enterprise 4 64-Bit version with the latest updates installed. I am still noticing that Excel is having issues when the Excel program memory goes above 1,800 MB. What is the deal? Does Excel have a limit on how much memory it uses? I did check my options and I'm using all threads with no limits listed. Is there a way to increase this limitation, if in fact one does exist? My Lap specs are; AMD Ryzen 7 PRO 7840U 8 Cores, 16 Threads Ram Memory 32 GB Microsoft Win 10 Pro Lenovo ThinkPad T16 Gen 216KViews0likes1CommentRe: Latest Version File Save Errors when File is in .xlsb format
Thanks for the advice as it hope will help others. As for me I had already did steps 1-3 previous to this post. However, how do I report the issue directly to Microsoft? As for work-a-rounds I mentioned one in my post and have been changing what files I can from .xlsb to .xlsm. However, most of my files require they be in .xlsb format. Makes for easier transfer of macro enabled files and to reduce file sizes.2.5KViews0likes0CommentsLatest Version File Save Errors when File is in .xlsb format
I am using MS Office 365 E4 Version 2401 (Build 17231.20236) Current Channel. Ever since I received this latest update I am having issues saving .xlsb files. I can open them but then I get the unable to save workbook is corrupted, blah, blah, blah. To fix the issue I have to copy my work to another workbook (so I don't lose it). Close the file with error out without saving. Then open the file back up under Open and Repair. The file is repaired and works just fine again. Not sure if this is an issue the MS team has identified and are working on in the next revision, but I hope it is because it is really annoying.Solved2.7KViews0likes2CommentsRe: Excel VBA to import data from Excel to Sharepoint Approval
It is looking that way. I have setup several PA processes so I am pretty familiar with it. Do you or anyone here know how to create a new approval but not submit it? Why you ask, because I want it to populate some data into the approval form automatically and leave it open and in view for the user to review, add the approvers and then the user selects submit. The approval form has a lot of data entries that the user has already done in an Excel sheet. I am preventing them from having to enter the data twice.2.2KViews0likes3CommentsExcel VBA to import data from Excel to Sharepoint Approval
Getting ready to start a project where I need to upload data from an Excel sheet into a Sharepoint site that has a custom Approval Form. I have a few ideas on how I might do it but with Excel, Sharepoint and Approvals App changing what seems to be on a daily basis. I would like to hear a few ideas from any one else who might have the time to share. The user will enter data into an Excel workbook and upon pressing the Macro Button named, Save & Submit, it will open the sharepoint site page, create a new approval using the custom approval form. I want it to populate matching data from the Excel file into the Approval request and then show the approval form for the user to review before sending. The user will be responsible of selecting the approvers that data will not come from Excel. I have never used VBA to open a Sharepoint Site page, input matching data, and leave form open before. Not even sure if this is even an option. As for matching input data it is as simple as Excel has a Cell that always holds First Name, Last Name, Category, Effective Date, Etc... Which match the fields in the Sharepoint Approval. The ideas I have for doing this, I feel are more complicated then I think it may need to be. Which is why I humbly ask for any input any one may have time to share. Oh, I am using MS 365 Enterprise version.2.3KViews0likes5CommentsPower Query Very Slow using get files from folder
I have a Master file that uses power query to import all xlsx files from a folder. Pretty simple setup, since all the files are setup (have the same headers 55 columns) and formatted the same way. Currently the folder that holds the files is up to 103 files at 145MB. When I open the Master file and select refresh all it takes up to five minutes for it to update. This is way too long. I used power query because I figured it would be the fastest way to update the master file as new files/data is added. Any suggestions on making it faster? I have already selected fast data mode, and data options within power query to make it as fast as I could. I have recently received the compatibility warning when I try and edit the query Queries in this workbook might be incompatible with your current version of Excel. Queries were authored with a newer version of Excel or Power Query and might not work in your current version. PQ version is 2.96.842.0 My PC specs easily exceed the requirements to refresh this data. I am using 365 Enterprise 3.9.8KViews1like1CommentExcel's xlookup formula not working in latest Build Version
I have a few folks using MS 365 E3 with the latest version 2202 (Build 14931.20120) Current Channel and when I use the xlookup formula it shows it as .xlfn in the formula bar when they open it up. That means that their version of Excel does not support that formula. IT has assured me they have the latest version of Excel so I am at a loss of what to do. I am using Version 2108 (Build 14326.20784) Semi-Annual Enterprise Channel. Anyone have any ideas on what I need to do? IT Could be wrong and they don't have the latest?752Views0likes0CommentsTo Power Query or Not to Power Query that is the Question
I have a table that has 140,000 rows with data and that is only 6 month's worth so it could potentially go up to 300,000 or more records by the end of the year. Originally I set up my workbook with a Dashboard Configuration with this table inside, along with multiple Pivot Tables (7 each) populating the Dashboard Data using Slicers. The issue was the file size was at 42MB and that is with it formatted as binary xlsb. So, I re-grouped and came up with an idea to remove this table and store the it in another workbook. Then I would use Power Query to link the Pivot Tables. At first I was excited because the workbook size fell from 42MB to just 2MB and the file was fast. For the Pivot Tables I unchecked "Save Source data with File" in Pivot Table Options. I also unchecked Refresh Data when opening the file options. In PQ I also disabled Enable Background Refresh and Refresh data when opening the file. The data in the Table will only change once a month and my plan was to refresh the data in the workbook when this occurred. My Issue is when I had another person open the file and try it out when they selected one of the slicers it stated the data had to be refreshed first (Not everyone has access to the table stored in another workbook and I don't want them to). So, I had them refresh anyway and it took a while (they had access but it took 3-4 mins to refresh). Once the refresh completed they were able to perform slicer selections successfully. Is there anyway to keep the file size low and make use of the data without refreshing it? If I don't use PQ and store the data within the file may get too large and too slow and no one will use it. If I use PQ and store the data remotely then while the file size may go down tremendously, data refresh has to occur and it takes way too long. I'm danged if I do and danged if I don't. On a side note: I got to learn Python so I can get myself out of these Excel nightmares.Solved1.2KViews0likes1CommentMSO 365 Sharepoint and File Sharing with Macros Included Potential Issues Question
My main question is will macros or Power Query cause Excel Workbook to not be shareable in Sharepoint? If so, is there a way around it? I created a file that includes 5 sheets that Power Query cleans and appends as one master file. It refreshes when user selects Macro button. I also have a Private Sub Worksheet_Calculate routine that runs when a sheet Calculates based on the target value it hides or unhides charts. Could any of these effect shareability in Sharepoint? The file is actually saved in Teams but Teams stores it in Sharepoint. I have had some off and on issues with users running into save warnings and errors where it asks one user if they want overwrite or Save a copy and the other user it just won't let them save. My thought process leads me to believe that if two users are in the file and each makes changes to one of the five worksheets and refreshes that this may create issues because I know share issues can occur if one user changes cell A1 in Sheet 1 and another user makes a different change in the same sheet and cell this will cause a save issue. This will not occur in the dataset that I have for the 5 worksheets. However, the data that is linked to those worksheets graphs, charts and pivot tables will constantly change. There has to be a way around this.819Views0likes0CommentsRe: VBA Not Visible after Microsoft Error and Repair Completed
Thanks for sending this my way. However, I may, or may not have found a solution that is much easier. For me it 100% worked and got my Excel working without errors this morning. I went in and cut my PERSONAL.xlsb (Personal Macro File) in C:\Users\Username\AppData\Roaming\Microsoft\Excel\XLSTART I then pasted it to my documents for the time being. Opened a new Excel workbook (My error of can't find library or object is missing went away). Went to Developer tab and recorded a new macro (which created a new personal account). I renamed the one I saved in my documents to something other than PERSONAL.xlsb. Opened it up and copied the code into my new PERSONAL.xlsb module and then removed the old one again. Works like new.3.4KViews0likes0CommentsRe: Excel Performance Issues and Errors when PC Usage is high
So, I finally think I have Excel's issues all figured out. I have been noticing when Excel starts using about 2-2.5gb of ram is where issues start occurring where Excel does all kinds of crazy stuff. I have a 16gb ram installed and one would think MS Office/Excel could easily run off of that. I have seen several posts in many other forums where users are finding the same issue at the same ram level. I am using Office 365 Apps for Enterprise 64-Bit. So, I should be able to utilize more than what it is limiting me too. I am on the most current version. So, after I completed an Online Repair I still get the Can't find project or library error when I open any file, even a blank brand new spreadsheet. My Reference Library is showing 2 Visual Basic For Applications checked one is pointed to Excel.exe and the other to the appropriate MSO.dll file. Since the one is connected to Excel.exe I can't remove it from the library. I do not want to do a full new install either. There has to be a way to access the reference library and remove this through the registry or something similar.2.5KViews0likes0CommentsExcel Performance Issues and Errors when PC Usage is high
I have been using Excel for over 25+ years and consider myself an expert. Excel cannot handle complex formulas and big data. Even without volatile formulas Excel can't handle much of anything you throw at it. As I type I sit and wait for now 12 mins because Excel is frozen. And why... because I deleted a column from a table. Holy crap. Make sure you don't ever paste from a FILTERED table into another sheet by accident either or you'll be waiting while Excel re-calculates each and every cell it pastes. Uggghhh FOREVER. Today went into a file to process a macro via VBA and I see the code in the project folder but when I clicked on my code would not show up in the window. Just a blank page where my career will one day be thanks to Excel. I had to open Excel as Safe Mode save the file and re-open it. Tables always seem to cause more headaches then they are worth. Excel has way too many flaws. We need better programmers working at Microsoft. Please someone make Excel better. I deal with this stuff DAILY.2.7KViews0likes13Comments
Groups
Recent Blog Articles
Re: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
sowjvellore On your Desktop in your Type Here to Search bar type in Task Scheduler and you should see a program appear named Task Scheduler. If you Google Use Task Scheduler to Open Existing Excel Wo...0likes0CommentsRe: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
Hey sowjvellore Very much agree with your position. Perhaps here is an alternative solution to your problem that you might find is the better option. I am using Task Scheduler to automate opening o...0likes0CommentsRe: New in Excel for the web: Power Query Refresh is now generally available for selected data sourc
Is it possible to use Power Automate to refresh all data connections in an Excel Workbook? I am getting so many conflicting answers to this when I google it. I have some Power Queries in a workbook s...0likes0Comments