delaying activity during script - vba

Question

Running the following query to grab some data from steam charts:

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://steamcharts.com/app/" & games(x), Destination:=Range("A" & lastRowData))
    .Name = " & games(x) & "
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=True
End With

Then I have other actions afterwords like

//Run analysis

The issue I'm running into is that while the script executes correctly, the "analysis" portion runs but doesn't work as expected because while the query loads, it takes maybe 5 seconds to do so, and the script executes immediately instead of waiting for the previous data to load before working. Is there a way to delay execution of subsequent script commands until all data is loaded in the spreadsheet?

See this link: https://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time-excel-vba — PKatona, Sep 05 '17 at 15:57
@PKatona the issue I have with this solution is that it's specifying a set amount of time. I'm really asking if there's a way for excel to acknowledge the query is still running and to wait as long as necessary until the data is populated to continue the script. — Joseph Erickson, Sep 05 '17 at 15:59

score 1 · Answer 1 · answered Sep 05 '17 at 16:19

Interestingly enough, for this issue, the answer was pretty simple. To clarify, I used both DoEvents and something like Application.Wait Now + #12:00:12 AM# (waiting more than enough time for the query to run and load). However, even using both of these methods, for some reason it seemed that the data would only load to the spreadsheet after all queries had run.

Experimenting, I simply changed the last line from .Refresh BackgroundQuery:=True to .Refresh BackgroundQuery:=False which was a wild guess, but it worked.

score 1 · Accepted Answer · answered Sep 05 '17 at 17:14

As you correctly guessed, the way to delay execution of subsequent commands is to set .Refresh BackgroundQuery:=False. The reason for this is actually obvious once you understand what this means.

From the built-in Excel Help

True to return control to the procedure as soon as a database connection is made and the the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet.

You are probably more familiar with the terms asynchronously and synchronously. False means to run the query synchronously.

The other way to achieve the same result is to run the query in the background (asynchronously) and in the following code poll the .Refreshing property until that becomes false. This allows you to do other things, such as displaying a custom progress bar.

There is also an AfterRefresh event you can hook into.

score 0 · Answer 3 · answered Sep 05 '17 at 16:04

0

Try using DoEvents right after the End With.

answered Sep 05 '17 at 16:04

PKatona

629
2
9
19

Doesn't work. `DoEvents` just allows *other* background processes to get execution time. It has nothing to do with VBA methods that run in the background. – robinCTS Sep 06 '17 at 00:39

delaying activity during script - vba

3 Answers3