假设你有3个查询:查询名1 查询名2查询名3 ,你想依次执行:
执行RefreshSequence过程即可(在excel中文版,注意在查询名前要加 “查询 - ”!)。
过程里面调用UpdatePowerQuery函数
---------------------------------------------------
Sub RefreshSequence()
UpdatePowerQuery "查询 - 查询名1"
UpdatePowerQuery "查询 - 查询名2"
UpdatePowerQuery "查询 - 查询名3"
End Sub
------------------------------------------------------
Public Function UpdatePowerQuery(sQueryName As String) As Boolean
'Written by Ken Puls (www.excelguru.ca)
'Function Purpose: Update Power Queries without background refresh
' (This allows completion before next step of macro is triggered)
Dim cn As WorkbookConnection
On Error Resume Next
Set cn = ThisWorkbook.Connections(sQueryName)
If Err.Number 0 Then
Err.Clear
GoTo NoConnection
End If
With cn
.OLEDBConnection.BackgroundQuery = False
.Refresh
.OLEDBConnection.BackgroundQuery = True
End With