how can I control the order in which Excel data connections are executed?

Question

I'm setting up an automized job that loops through a stack of SQL Data each time the data connections of the Excel Worksheet are updated and saves the Worksheet under a dynamically set name. I have 3 data connections that all point to SQL Stored Procedures. Technically speaking everything works fine, but now I realised that the order of the 3 executed data connections varies unpredictably. This causes that the data is not synchronized. The loop parameter is set by the first stored procudere, so this is the one that must be executed first. How can I control exactly in which order these connections are executed.

I logged the connection ID order during the call of the stored procedure: (3,2,1), (3,1,2), (1,3,2) - the following 4 loops didn't change

I'm quite an Excel Hater and everytime I need to work with it, it drives me to the edge of self-control. How can it be that these orders are changing? How to control it.

Martin

Answer 1

You should be able to control the query order of execution in VBA.
First ensure the "Background Refresh" settings are turned off in the Query Properties.

Then in the code you run from your button, specify each individual query refresh in the desired sequence and (just to be explicit), use the "BackgroundQuery:=False" option, so that each query finishes before the next starts.

Sheet1.QueryTables("QueryName1").Refresh BackgroundQuery:=False
Sheet2.QueryTables("QueryName2").Refresh BackgroundQuery:=False
Sheet2.QueryTables("QueryName3").Refresh BackgroundQuery:=False