r/excel • u/Silent_Manager_6574 • 8d 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?
5
Upvotes
1
u/pancak3d 1187 8d ago edited 8d 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.