让 Excel VBA 在继续之前等待外部工作簿链接刷新

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

提问by Rasmus Remmer Bielidt

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.

首先，它从 SQL 服务器更新 ODBC 数据表，然后更新其中一个数据表，以包含对另一个 Excel 电子表格的引用的公式。

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?

但是，它会在完成刷新源表中的数据之前更新数据透视表，从而导致源表之间的数据不一致（即使在重建数据透视表时也会刷新）。有没有办法让 VBA 在继续之前等待外部链接的完全刷新？

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

回答by Reafidy

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

您需要检查连接属性并禁用“启用后台刷新”选项。

Or set the backgroundquery property to false in your code.

或者在您的代码中将 backgroundquery 属性设置为 false。

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

这应该强制 excel 在继续之前等待查询完成。

回答by steveno

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

我有这个问题；Readify 的回答恰到好处。这是我的代码。

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