Excell RefreshAll runs after other macros

Question

I want to refresh my workbook and then run a check on a cell and if value > 0 display a message, the code i have seems right and logical but the refresh is done after the check value, i have tried spliting these into seperate macros and calling them in sequence but the refresh still runs after the check. not sure if it is worth noting that the refresh involves refreshing data connections to an SQL DB.

this is the two macros i have at the moment :

Sub RefreshMacro()

ActiveWorkbook.RefreshAll
Sheets("Execution").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("Traffic Lights").Select
ActiveWorkbook.RefreshAll

End Sub
Sub ErrorMessage()
If Sheets("Traffic Lights").Range("G2").Value > "0" Then
MsgBox "Error with data!" & vbCr & 
"Please Note There is an issue with the data" & vbCr & 
"See Traffic Lights for more details!", vbOKOnly + vbExclamation, 
"Red Traffic Lights"
End If
End Sub

Take a look at http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba — Cody Ｇ, Sep 21 '16 at 15:29
Also to note i am reasonably new to VBA, and i no i have the refresh in twice in the posted example i have been trying all sorts to get it working this is just the current state — craig booth, Sep 21 '16 at 15:30
i have tried that at the end of the first macro and it makes no difference — craig booth, Sep 21 '16 at 15:33
tried the second solution and that fails with Variable not defined — craig booth, Sep 21 '16 at 15:44
Sub Refresh_All_Data_Connections() For Each objConnection In ThisWorkbook.Connections 'Get current background-refresh value bBackground = objConnection.ODBCConnection.BackgroundQuery 'Temporarily disable background-refresh objConnection.ODBCConnection.BackgroundQuery = False 'Refresh this connection objConnection.Refresh 'Set background-refresh value back to original value objConnection.ODBCConnection.BackgroundQuery = bBackground Next MsgBox "Finished refreshing all data connections" End Sub — craig booth, Sep 21 '16 at 15:45
Try [removing the `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and work directly with the sheets. Your first macro is just one line, between the `RefreshAll`: `Sheets("Execution").PivotTables("PivotTable1").PivotCache.Refresh` Also, what variable was not defined when you tried `DoEvents` suggestion? — BruceWayne, Sep 21 '16 at 16:49

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

Here are the two solutions based on this link

First Solution:

Sub CheckTrafficLights1()


    ActiveWorkbook.RefreshAll
    DoEvents
    Sheets("Execution").PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents ' Not sure if necessary.

    If Sheets("Traffic Lights").Range("G2").Value > "0" Then
        MsgBox "Error with data!" & vbCr & 
        "Please Note There is an issue with the data" & vbCr & 
        "See Traffic Lights for more details!", vbOKOnly + vbExclamation, 
        "Red Traffic Lights"
    End If
End Sub

Second solution:

Sub CheckTrafficLights2()

    For Each objConnection In ThisWorkbook.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
    Sheets("Execution").PivotTables("PivotTable1").PivotCache.Refresh
    DoEvents ' Not sure if necessary.

    If Sheets("Traffic Lights").Range("G2").Value > "0" Then
        MsgBox "Error with data!" & vbCr & 
        "Please Note There is an issue with the data" & vbCr & 
        "See Traffic Lights for more details!", vbOKOnly + vbExclamation, 
        "Red Traffic Lights"
    End If
End Sub

edited May 23 '17 at 10:27

Community

1
1

answered Sep 21 '16 at 17:05

Cody Ｇ

8,368
2
35
50

1st solution fails to update the data connections before the error message is displayed – craig booth Sep 22 '16 at 07:27
2nd Solution produces an application -defined or object defined error (run-time error 1004) – craig booth Sep 22 '16 at 07:28
on the second solution i have changed the connections to ODBCConnection as this is what i am using , Still fails as above – craig booth Sep 22 '16 at 07:28
I have also stripped the second solution down to just the refresh (not changing the background settings) and then the message and this still fails as with the other solutions the data connection is updated and refreshed after the message is generated. – craig booth Sep 22 '16 at 07:51
also worth a mention when i step through t it works as expected this is only an issue when it is run as a whole – craig booth Sep 22 '16 at 08:17
i have also tried adding a wait in case the DoEvents is overlooked some how and the refresh is still not completing till after the message box is closed – craig booth Sep 22 '16 at 08:25

score 0 · Accepted Answer · answered Sep 22 '16 at 09:23

Ok so i have tried all sorts to get this to work and have managed with the below, This seems to do the trick:

Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

as in the below full query

Sub CheckTrafficLights2()
    For Each objConnection In ThisWorkbook.Connections
             objConnection.Refresh
DoEvents
    Next
ThisWorkbook.RefreshAll

Application.CalculateUntilAsyncQueriesDone
Application.CalculateFullRebuild
Application.CalculateUntilAsyncQueriesDone

Sheets("Execution").PivotTables("PivotTable1").PivotCache.Refresh
    If Sheets("Traffic Lights").Range("G2").Value > "0" Then
        MsgBox "Error with data!" & vbCr & "Please Note There is an issue with the data" & vbCr & "See Traffic Lights for more details!", vbOKOnly + vbExclamation, "Red Traffic Lights"
    End If
End Sub

Excell RefreshAll runs after other macros

2 Answers2