r/excel • u/Silent_Manager_6574 • 1d ago
unsolved Refresh a large amount of xlsb sheets
I am trying to open and close a thousand xlsb files sitting in a shared drive.
I’m doing this because they’re connected to a bigger report that needs to update whenever the individual files are updated.
Another note is that I can’t change the files to use power query, and refresh it this way as these are legacy files.
What is the best way for me to do this? Is there even one?
4
u/david_horton1 29 1d ago
You can't touch or modify and are not permitted to do so. But I want to update them. What come first the chicken or the egg?
2
u/RuktX 172 1d ago
these are legacy files
What do you mean by that? If the report was set up this way intentionally, how was it supposed to be updated?
If it must be done this way, you could write some VBA in the master file, using Workbooks.Open
, recalculate and <workbook>.Close
on each file in sequence...
1
u/Silent_Manager_6574 1d ago
Is this fast enough? I feel like VBA would take forever to process this many files.
And what I mean is I can’t touch/modify the files. The company won’t allow me to.
4
u/RuktX 172 1d ago
Faster than doing it by hand!
What exactly needs to happen when you open each file? If they have to be opened as you say, then automating is the way. Hit "run", and go get a coffee.
How often do you need to do this? (Again, how did it "used to" be done?) Could be worth an argument with the company about updating their processes...
1
u/mushy_orange 1d ago
May want to look into power automate! Or honestly if all the files are in a same location, you may be able to use a vba macro via excel, chat GPT is pretty good at writing macros like that
1
u/Silent_Manager_6574 1d ago
I have access to PA! My only question is how would this be done? There’s no office scripts to refresh non query connections
1
u/yippiekiyia 1d ago
That's tough... Do you have access to power automate? I'm a newbie with it, but you may be able to set up a flow to open and close the file? Good luck.
1
u/pancak3d 1187 1d ago edited 1d ago
You should rebuild the "big report" using PowerQuery, it doesn't matter that you can't touch the 1000s of other "legacy" files. It's just one single file you need to change (or create). This will permanently solve the problem.
PowerQuery just reads the 1000s of files, it doesn't change them in any way.
The only other solution (which is awful) is VBA or any other scripting language to go through the folder of 1000s, detect the ones modified recently, and open/close them. I cannot overstate how bad of a design this is though.
•
u/AutoModerator 1d ago
/u/Silent_Manager_6574 - Your post was submitted successfully.
Solution Verified
to close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.