0

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

Barnabeck
  • 459
  • 1
  • 6
  • 26
  • 1
    Please show us how you call the procedures. My guess is that they are called with a `For ... Each` loop which doesn't specify a sequence and you should try calling them by their index numbers. On the other hand, if you do call them by index number they are stored by the `For ... Each` method and they must be identified by name. – Variatus Nov 14 '20 at 01:26
  • I haven't reached that point of programming the macro. I am still manually updating the data connection with the button: Update All connections – Barnabeck Nov 14 '20 at 01:32
  • #Variatus Ok. Thinking about your answer makes me understand that I can call the connection separatly. I'm a very basic Excel user and I always used the UpdateAll command in a prior version of that Excel file. That sound like a possible way. But you have to agree that pushing the UpdateAll button should execute the connections in one predictable (alphabetical?) order – Barnabeck Nov 14 '20 at 01:41
  • No. In many instances Excel will create indices depending upon sequence of creation or loading. What, for example, is the sequence of workbooks in memory? – Variatus Nov 14 '20 at 01:45
  • @Variatus, sorry but I don't understand your question. I have 1 connection (1) in worksheet1 and 2 in worksheet2 (2 & 3). Both connection of worksheet2 are linked to worksheet1. The Sequence of the loading is the one described above. I just push the UpdateAll button all the time. What indexes are changing? It looks like a totally stable repetitive task to me – Barnabeck Nov 14 '20 at 01:52
  • Sorry, it was a rhetorical question intended to show that the connections can have a sequence in memory other than alphabetical by name. All connections are grouped into a `Collection` which is a programming construct in which each member has an index number. However, I don't know at which time the `Collection` you are referencing is created internally. It might be at the time of your pressing the button. In that case the sequence would depend upon storage elsewhere where the connections were stored and indexed in the sequence in which they were created. – Variatus Nov 14 '20 at 01:56
  • @Variatus Ok, whatever. Thank you for the hints and info, I know now how I could workaround the problem by calling the 3 connections separatly. But I will solve it most likely in SQL by introducing seperate loop counters for the 3 connections. After all it still looks like a buggy behavior to me. – Barnabeck Nov 14 '20 at 02:08

1 Answers1

0

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.

enter image description here

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
user9601310
  • 1,076
  • 1
  • 7
  • 12
  • Interesting point! Yes, one actually can control the order in the macro script. BUT, the fact that hitting the "Update all button" causes the connection to be executed in an order sequence that changes all the time is so highly frustrating and buggy that I changed my queries in a way that the order doesn't matter anymore. Excel and I will never be friends. Last frustrating discovery I will have to investigate on: My query works with dynamic SQL that returns column headers based on the language related to that Data Set that can differ every Refresh... and Excel doesn't digest this – Barnabeck Nov 15 '20 at 15:40