excel - 让 Excel VBA 等待外部工作簿链接刷新后再继续

Question

我有一个工作表，它可以做两件事。

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

其次，它刷新基于上面更新表中的数据的数据透视表。

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

这是我的代码:

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

Answer 1

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

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

这将强制 Excel 等待查询完成后再继续。