I'm looking for a way to automate the process of opening multiple Excel templates (.xltx) in a folder and refreshing all the queries in them. Normally, I have to do this manually whenever the master template changes, which involves opening each template, refreshing the queries with Ctrl + Alt + F5, saving, and closing it, then moving on to the next one. I have about 10 templates in total, and I was told that PowerShell might help automate this. I want a method I can run on my own schedule, not automatically. Any tips on how to set this up?
1 Answer
You could look into using the ComObject for Application.Excel in PowerShell. It allows you to control Excel directly. However, if you need the templates to refresh only when you trigger it, a VBA macro embedded in the actual templates might be a better solution. That way, you can specify exactly what happens when you run it.
Thanks for the suggestion! I'm actually looking for a one-time command to refresh the connections without it linking to changes in the master template. Any thoughts on how to set that up?