•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Fastest way to automatically manipulate excel files, ideally without opening them.
Our take
Automating Excel file manipulation can significantly enhance efficiency, especially when it involves extracting and processing data across multiple files. Utilizing VBA macros often leads to bottlenecks due to the necessity of opening and saving large Excel files. Exploring alternatives like Python with the Pandas library may offer a faster solution by allowing direct manipulation of Excel file contents without the overhead of launching Excel. Additionally, PowerQuery could streamline data transformations, providing a progressive approach to managing complex workflows.
I’m (re)designing an automated system we have that uses macros to:
a) extract info from an individual “instruction” file (using VBA class variables to store the data)
b) creates folders, copies over files, updates named ranges in certain files, copies certain tabs from certain files into other files, creates .csvs of certain tabs within files. Lots of stuff, but all essentially creating new excel files and using their contents to manipulate or create more excel files.
c) repeats for multiple other individual files
Basically, a lot of using vba to open some or other file, edit it, save it, repeat. This can only really go as fast as Excel can run, since everything gets bottlenecked by eg. opening and saving some large Excel file.
I’m sure there must be a way to read / manipulate certain parts of Excel files without necessarily opening them in instances of Excel, which is the major time/resource consuming bit. Is python with the Pandas library any faster than VBA for this sort of thing? Everyone is always praising PowerQuery but not sure how it could potentially fit in here.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Do you use VBA regularly or have you moved entirely to Power Query and formulas?I have been using Excel for years and VBA was always my go to for automation. Lately I have been seeing more people say they barely touch VBA anymore because Power Query and dynamic arrays cover most of what they need. I still use VBA for things like automating reports across multiple files or generating custom email bodies from data. But I am wondering if I am behind the curve. For those of you who work in data heavy roles, what is your current workflow? Do you still use VBA regularly or have you replaced it with other tools? Curious if I should be spending more time learning Power Query and the newer formula features instead of maintaining my VBA skills. submitted by /u/ProfessionalOk4935 [link] [comments]
- I’ve been using Excel more lately and I’m trying to understand some of its more advanced features without making everything overly complicatedWhen working with data that has multiple conditions or needs to update automatically, what are the most efficient functions or tools to use? for example, is it better to rely on formulas like XLOOKUP and FILTER, or are there built in tools that handle this more cleanly? Also, how does excel handle performance when formulas start getting longer or more complex? Is there a point where using too many formulas slows things down significantly? What are the best built-in features in Excel for handling complex data in a simple way? submitted by /u/icepix [link] [comments]
- how did you improve your workplace's legacy vba macros?I recently transitioned to a non-clinical role in a public health care system. part of the on-boarding was a 12 page, 20 step tutorial on how to 'do the macros'. The workflow simplified is: - Get source data from EHR/BI - Open the excel online (microsoft 365) "Daily Review" workbook in the desktop ms excel. (hopes and prayers it doesn't crash) - copy data (columns of patient ID, demographics, medications... you get the idea) from EHR, paste into this Daily Review - run macro (click a button) which cleans, filters, applies conditional formatting i think - save - go back to excel online and resume editing there. The VBA code was created (not sure if it was written coz it has no documentation) by a colleague who is on extended mat leave. I can see a lot of 'modules'. Can't tell which is active. There are probably lots of historical decisions. The daily review file with its many many sheets is saved in multiple locations in case newcomers like me or others break it by accident. I am told we can't change anything like move a column closer to the beginning coz well we can't. I don't know VBA but could probably figure it out if I watch a tutorial on it. I am linux user and know basics of C, python and make good use of my claude code with the pro subscription but never really worked with spreadsheets. I am wondering if anyone was in a similar situation and how you managed it. Is moving to office scripts (typescript) a viable alternative? Any other life improving tips would be appreciate it. Or maybe I should just give up and focus the energy elsewhere? submitted by /u/Neat-Badger-5939 [link] [comments]
- I’m researching how people automate repetitive Excel/CSV workflows in day-to-day work.I’m building a desktop tool to set up these processes visually, but before going further I want to understand what actually hurts today. I’m not selling anything in this post, just trying to learn from real-world cases. If you work with this kind of thing, I’d really appreciate your input: - What was the last annoying Excel/CSV workflow you automated? - How often does it run? - What was the most frustrating part: combining files, cleaning data, standardizing columns, deduplicating, exporting, etc.? - Are you the only one who runs it, or do other people need to run it too? - What do you use today: Power Query, VBA, Python, Power BI, something else? Thanks! submitted by /u/Remote-Ad-6629 [link] [comments]
Tagged with
#Excel alternatives for data analysis#Excel compatibility#Excel alternatives#generative AI for data analysis#natural language processing for spreadsheets#real-time data collaboration#rows.com#big data management in spreadsheets#conversational data analysis#automated anomaly detection#large dataset processing#financial modeling with spreadsheets#intelligent data visualization#real-time collaboration#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#Excel files