VBA MsgBox After Background Query Completion?-CodePudding

I have created a VBA code to import data from CSV convert it into a table and refresh the query that is already setup.

I want the user to be informed when the background query is completed by displaying a VBA msgbox.

PQ here

I tried below code but it doesn't work because if condition would be nothing by the the time query is completed. So no msgbox will be display.

Do I need to setup some delay like 15 sec and display msgbox anyway but then it wouldn't be a good idea.

How to sync background query completion with VBA msgbox?

ThisWorkbook.RefreshAll 
Sheets(2).Select 
If Sheets(2).Range("AG3").Value <> "" Then MsgBox "Completed"

CodePudding user response：

The Delay is tricky. Sometimes it could be 2 seconds or 10 to update the whole data, and also when the data gets bigger, the system will need more time to update the Data Model. This means that the "MsgBox" could appear when the data still updating.

I understand the perks of the Background Update, but it is important to know that it stops when you save the workbook. Instead, I would block any activity in the workbook until the Data Model is complete updated. For this, I use the following code that I found here long time ago:

Sub Aktualisieren()

On Error Resume Next

Application.DisplayAlerts = False
Application.ScreenUpdating = False

    With ThisWorkbook
        For Each objConnection In .Connections
            'Get current background-refresh value
            bBackground = objConnection.OLEDBConnection.BackgroundQuery
            'Temporarily disable background-refresh
            objConnection.OLEDBConnection.BackgroundQuery = False
            'Refresh this connection
            objConnection.Refresh
            'Set background-refresh value back to original value
            objConnection.OLEDBConnection.BackgroundQuery = bBackground
        Next
        'Save the updated Data
        .Save
    End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Data Model Updated"
End Sub