Getting Excel VBA to wait for external workbook links to refresh before continuing

3
votes

I have a worksheet which does two things.

First it updates ODBC data tables from a SQL server, then updates one of the data tables to include formula with references to another excel spreadsheet.

Secondly, it refreshes a pivot table that is based on data from the updated table above.

However, it updates the pivot table before it has finished refreshing the data in the source table leading to inconistent data between the source table (which is refreshing even as the pivot table is rebuilding). Is there any way to ask VBA to wait for a full refresh of external links before continuing?

This is my code:

Sub Update_All()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual
ODBC_Tables_Update
UpdateReqTable
' force a calculation of the table due to insert of formula and lookup in the campaign plan
With Application
    .CalculateFull
    .Calculation = xlCalculationAutomatic
End With
'update pivot after calculation is done
OverviewUpdatePivot

Application.ScreenUpdating = True

End Sub

Private Sub ODBC_Tables_Update()

With ActiveWorkbook
    .Connections("Pallet Requirement").Refresh
    .Connections("Pallets on Stock").Refresh
End With

End Sub

Private Sub OverviewUpdatePivot()

With Sheets("Overview").PivotTables("pvt_PalletOverview")
    .PivotCache.Refresh
    .PivotFields("Start Date").AutoSort xlAscending, "Start Date"
    .PivotFields("PALLETITEM").AutoSort xlAscending, "PALLETITEM"
    .PivotFields("Start Date").ShowDetail = False
End With

End Sub

Private Sub UpdateReqTable()

' general variables
Dim cpPath$ 'Campaign Plan Path
Dim cpName$ 'Campaign Plan File Name
Dim cpL93$ 'Sheet 93 name
Dim cpL94$ 'Sheet 94 name
Dim cpL96$ 'Sheet 96 name
Dim ListNoCol$ 'Column Letter of List Numbers
Dim StartDateCol$ 'Column Letter of "Start Date"
Dim tblMatchPart$ 'JOBLIST field of table

' below index building part strings defined
Dim cpL93ListNoRange$
Dim cpL94ListNoRange$
Dim cpL96ListNoRange$
Dim cpL93DateRange$
Dim cpL94DateRange$
Dim cpL96DateRange$
Dim L93MatchFormulaPart$
Dim L93IndexFormula$
Dim L94MatchFormulaPart$
Dim L94IndexFormula$
Dim L96MatchFormulaPart$
Dim L96IndexFormula$

' Fill in values from the sheet into variables
cpPath = Range("cpPath")
cpName = Range("cpName")
cpL93 = Range("cpSheetL93")
cpL94 = Range("cpSheetL94")
cpL96 = Range("cpSheetL96")
ListNoCol = "$" & Range("cpListNoCol") & "1" & ":" & "$" & Range("cpListNoCol") & "64000"
StartDateCol = "$" & Range("cpStartDateCol") & "1" & ":" & "$" & Range("cpStartDateCol") & "64000"
tblMatchPart = "tbl_PalletReq[@JOBLIST]"

' Build the range expressions used in the MATCH and INDEX formula
cpL93ListNoRange = "'" & cpPath & "[" & cpName & "]" & cpL93 & "'!" & ListNoCol
cpL93DateRange = "'" & cpPath & "[" & cpName & "]" & cpL93 & "'!" & StartDateCol
cpL94ListNoRange = "'" & cpPath & "[" & cpName & "]" & cpL94 & "'!" & ListNoCol
cpL94DateRange = "'" & cpPath & "[" & cpName & "]" & cpL94 & "'!" & StartDateCol
cpL96ListNoRange = "'" & cpPath & "[" & cpName & "]" & cpL96 & "'!" & ListNoCol
cpL96DateRange = "'" & cpPath & "[" & cpName & "]" & cpL96 & "'!" & StartDateCol

' Build the INDEX formulas required
L93MatchFormulaPart = "MATCH(" & tblMatchPart & "," & cpL93ListNoRange & ",0)"
L93IndexFormula$ = "INDEX(" & cpL93DateRange & "," & L93MatchFormulaPart & ",0)"
L94MatchFormulaPart = "MATCH(" & tblMatchPart & "," & cpL94ListNoRange & ",0)"
L94IndexFormula$ = "INDEX(" & cpL94DateRange & "," & L94MatchFormulaPart & ",0)"
L96MatchFormulaPart = "MATCH(" & tblMatchPart & "," & cpL96ListNoRange & ",0)"
L96IndexFormula$ = "INDEX(" & cpL96DateRange & "," & L96MatchFormulaPart & ",0)"

'Insert Formulas into table tbl_PalletReq and format the formulas
With Range("tbl_PalletReq[L93 Date]")
    .Formula = "=" & L93IndexFormula
    .NumberFormat = "ddd-dd-mm-yyyy"
End With
With Range("tbl_PalletReq[L94 Date]")
    .Formula = "=" & L94IndexFormula
    .NumberFormat = "ddd-dd-mm-yyyy"
End With
With Range("tbl_PalletReq[L96 Date]")
    .Formula = "=" & L96IndexFormula
    .NumberFormat = "ddd-dd-mm-yyyy"
End With
With Range("tbl_PalletReq[Start Datetime]")
    .Formula = "=IFERROR([@[L93 Date]],IFERROR([@[L94 Date]],IFERROR([@[L96 Date]],"""")))"
    .NumberFormat = "ddd-dd-mm-yyyy hh:mm"
End With
With Range("tbl_PalletReq[Start Date]")
    .Formula = "=DATE(YEAR([@[Start Datetime]]),MONTH([@[Start Datetime]]),DAY([@[Start Datetime]]))"
    .NumberFormat = "ddd-dd-mm-yyyy"
End With
With Range("tbl_PalletReq[Est. Pallets]")
    .Formula = "=ROUNDUP(-[PROD.TONS]*1000/VLOOKUP([@PALLETITEM],tbl_PalletData,2,FALSE),0)"
    .NumberFormat = "#,##0"
End With

End Sub
2
excelvba

2 Answers

1
votes

You need to check your connections properties and disable the "Enable Background Refresh" option.

Or set the backgroundquery property to false in your code.

This should force excel to wait for the query to finish before continuing.

0
votes

I had this problem; Readify's answer was spot on. Here's my code.

    Range("Table_sqlserver_database[[#Headers],[column_name]]").Select
    With Selection.ListObject.QueryTable
        .BackgroundQuery = False
    End With