•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to batch process and refresh multiple excel files in parallel?
Our take
Are you looking to optimize the process of refreshing multiple Excel files? Automating the refresh of 116 files sequentially can be time-consuming, taking up to two hours. However, by mimicking your efficient manual method, where you batch process files and trigger refreshes concurrently, you can significantly reduce this time. In this discussion, we’ll explore how to create a Python or PowerShell script that allows you to refresh these files in parallel, streamlining your workflow and enhancing productivity.
Hi all,
I'm looking for an efficient way to automate the refreshing of 116 Excel files located in a single directory.
- Each of the 116 files runs a data query to an ERP that takes 40-60 seconds to complete.
- My current scripts (in Python, PowerShell, and VBA) process the files sequentially. This means the total time is roughly 116 files * 1 minute/file ≈ 2 hours, which is too slow.
My manual process is much faster (20-40 minutes total) because I process files in batches:
- I open a batch of about 14 files at once.
- I trigger "Refresh All" on each of them. Since the queries run in the background, by the time I've triggered the last file, the first ones are nearly done.
- I then go through the batch, saving and closing each file.
- I repeat this for the next batch until all 116 files are done.
How can I create a script (ideally in Python or PowerShell) that mimics this parallel, batch-based approach? I need a solution that can manage multiple files concurrently to be faster than my manual method, instead of processing them one by one. The script must wait for all data queries to finish refreshing before it saves and closes the files in a batch.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Best way to automate data refresh for multiple power queries pulling from web sources?Ive got a workbook with about 15 power queries pulling data from different web sources. Some need to refresh daily, others weekly. Right now Im just clicking refresh all manually but its getting tedious. I tried setting up a VBA macro to refresh them on open but it slows down startup a ton. Anyone found a solid way to schedule these refreshes or optimize the process? Considering power automate but not sure if its overkill. Curious how others handle this since my data sources are pretty stable at this point. submitted by /u/daverskully [link] [comments]
- Any faster way to merge large Excel reports automatically?I am working with financial and operational data in Excel and facing a recurring issue. Every day multiple reports are generated separately and each file contains thousands of rows of data. The challenge is that I have to manually combine all these reports into a single dataset before doing any analysis or building dashboards. Even using Power Query and sorting takes a significant amount of time when the files are large. Is there a more efficient approach to automate this process. Ideally something that can automatically pull multiple files and merge them into one structured dataset. Has anyone dealt with something similar? Would appreciate any suggestions or tools that could make this faster. submitted by /u/WoodpeckerNo5214 [link] [comments]
- Tools limited. How to automate multiple SQL server queries -> Excel workflow at work?Hi everyone, The initial process was to use a macros enabled excel template for data cleaning and reconciliation (we can still use macros but just this process alone takes a long time to get thru thousands of accounts cos each account needs to be reconciled). I would, -> run a couple of different queries in sql server -> copy & paste results into the excel template -> clean and reconcile debit/credit -> color code and mark tabs to be sent to manager for approval along with a sox template. I need this entire process automated somehow. My permissions are limited so at this point I can only work with sql, excel & power query based on my research (I don’t have prior experience with power query) Has anyone here done something similar before cos I could use some advice. I am trying to see how to integrate the many queries into this as well as what the end product should look like. I just want to create a more efficient process so that I can show my managers and perhaps they can incorporate it in a bigger scale if applicable. Thanks in advance! submitted by /u/Acrobatic_Sample_552 [link] [comments]
- Fastest way to automatically manipulate excel files, ideally without opening them.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. submitted by /u/PedosWearingSpeedos [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#real-time data collaboration#Excel compatibility#Excel alternatives#big data management in spreadsheets#conversational data analysis#intelligent data visualization#real-time collaboration#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#large dataset processing#cloud-based spreadsheet applications#natural language processing#Excel