Automatically run data connections in XLSM

问题:

I have an XLSM file which contains 2 Web Query connection. When I go to "Connections" in the "Data" tab I am presented with the two connections I have.

For each of these I can edit some properties, one which says "Update every X minutes". I've set this to 1 minute and also ticked "Activate background update".

This, however, won't work as the web query connections aren't run anyway.

Ultimately what I need is to run these connections automatically once every hour. Preferably without any user interaction and without the document being open.

Is this possible?

回答1:

You can't refresh a connection without the file being open. You can run queries on opening the file or create a VBA routine that opens the file, then uses the RefreshAll and saves over the original file every hour.
You can use VBS too.
The final solution by OP:

I ended up making a little VBS script to handle the open, refresh, close. Then a batch script wrapper to handle running the VBS script and logging. Finally Windows Task Scheduler to run the batch script periodically.