CODEWITHSUNDEEP
vbaexcel
Grace A
Grace A

Reputation: 175

Capture / Read message displayed in the Excel Application status bar

I have created VBA code that updates an Excel sheet, refreshes the Data Connection and saves the Excel sheet.

The problem : while the Data connection is still getting refreshed, the VBA code moves to the next step of Saving the Excel, and a pop-up prompting the user to Cancel the refresh appears (which it shouldn't because the refresh has to happen and a user input defeats the purpose of the VBA code). Below is a snippet:

 wkb.Connections("Connection2").Refresh
    While (InStr(1, Application.DisplayStatusBar, "Running background query") > 0)

    Wend
 wkb.Save
 wkb.RefreshAll
    While (InStr(1, Application.StatusBar, "Running background query") > 0)

    Wend
 wkb.Save

enter image description here Question : How can I capture this text in the Status Bar so that I can wait till it disappears? Application.StatusBar does NOT do the trick.

Upvotes: 3

Views: 1991

Answers (1)

Alex D
Alex D

Reputation: 696

Here's a few things to try:

1) Go to the connection properties and uncheck "Enable background refresh". It should wait for the refresh to end before executing your next line of code. Or in code:

ActiveWorkbook.Connections("CONNECTION_NAME").ODBCConnection.BackgroundQuery = False

2) Try the method CalculateUntilAsyncQueriesDone

Connections("CONNECTION_NAME").Refresh
Application.CalculateUntilAsyncQueriesDone

If you're working in Excel 2010 or later it should wait until this is all done.

3) Possible third option, just a DoEvents command

Connections("CONNECTION_NAME").Refresh
DoEvents

Upvotes: 2

Related Questions