0

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?

Linora
  • 10,418
  • 9
  • 38
  • 49
  • 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. – user3819867 Nov 03 '16 at 10:07
  • You mean like a VBA script which can be scheduled in f.ex. Windows Task Scheduler? – Linora Nov 03 '16 at 10:35
  • I meant a `VBA` script as the thing that you can write in Excel itself but ou can use `VBS` too. – user3819867 Nov 03 '16 at 10:57
  • 1
    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. – Linora Nov 03 '16 at 20:10
  • Can you answer the post so I can accept? - You got me on the right track :) – Linora Nov 03 '16 at 20:10
  • It's not about getting points, feel free to write your own answer elaborating how you got there and then you can accept your answer. As this thread stands now it's not going to help anyone else. – user3819867 Nov 09 '16 at 16:59
  • I agree it's not about the points :) – Linora Nov 10 '16 at 20:16

1 Answers1

2

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.

user3819867
  • 1,114
  • 1
  • 8
  • 18
  • 1
    To elaborate on this: I set up a task in Windows Task Scheduler which runs once every hour. The task executes a batch script which log a start and finish message as well as logs the output from the VBS script. The VBS script handles everything related to the XLSM file, i.e., opening, refresh data connections, closing. The entire process takes under 1 sec, incl. running 2 web queries. This approach seems to work as intended and this is what I will use. – Linora Nov 10 '16 at 20:23