VBA MsgBox After Background Query Completion?

Question

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"

user_14315926 · Answer

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

Now, as you ask, if you want to update one specific Query, first you need to know the specific name of it (it is not always the same as you have written). For this run the following code:

Sub Get_Conection_Names()
    With ThisWorkbook
     'Check if there is any conection
        If .Connections.Count = 0 Then Exit Sub
     'Print the numer of conection (item number) and its name
        For X = 1 To .Connections.Count
            Debug.Print X & ": " & .Connections.Item(X).Name
        Next X
    End With
End Sub

With the information of the Query, you can add to a bottom the following code:

Sub UpdateConectionbyName()
    Dim ConectionName As String
    ConectionName = "Query - Name of Query"
    With ThisWorkbook
     'Check if there is any conection
        If .Connections.Count = 0 Then Exit Sub
     'Check the connections names if one macht with the one we want.
        For X = 1 To .Connections.Count
            If .Connections.Item(X).Name = ConectionName Then .Connections.Item(X).Refresh
        Next X
    End With
End Sub

And now, if you want to Refresh a specific Query unabeliing the Background Update, this code will help. Item 1 is the number of the query. You can get it with the code above.

With ThisWorkbook.Connections
    bBackground = .Item(1).OLEDBConnection.BackgroundQuery
    .Item(1).OLEDBConnection.BackgroundQuery = False
    .Item(1).Refresh
    .Item(1).OLEDBConnection.BackgroundQuery = bBackground
End With

VBA MsgBox After Background Query Completion?

1 Answers