如何使用VBA在Excel中异步刷新Power Query查询生成的某些Ranges？

lokaqttq 于 2023-03-24 发布在其他

关注(0)|答案(1)|浏览(276)

Stack Overflow中有一些帖子说明了如何：

异步刷新所有查询
逐个刷新某些查询（即非异步）

如何使用VBA在Excel中刷新由Power Query查询异步生成的某些范围（* 例如，给定这些范围名称的数组 *），并在检测到这些异步刷新完成后在VBA中执行后续句子。
我尝试过的方法包括：

Sub fail_1()
'This method cannot guarantee showing the msgbox at the end of this sub AFTER the two ranges are refreshed
    arrRngName = Array("rng1","rng2")
    For Each itm in arrRngName
        Range(itm).ListObject.QueryTable.Refresh BackgroundQuery:=True
    Next itm
    MsgBox "All the refreshes are done asynchronously" 'This is an example of the subsequent sentence
End Sub

Sub fail_2()
'This method cannot guarantee neither that showing the msgbox at the end of this sub AFTER the two ranges are refreshed
    arrRngName = Array("rng1","rng2")
    For Each itm in arrRngName
        With ThisWorkbook.Connections("Query - " & itm).OLEDBConnection
            .BackgroundQuery = True
            .Refresh
        End With
    Next itm
    MsgBox "All the refreshes are done asynchronously" 'This is an example of the subsequent sentence
End Sub

Sub fail_3()
'This method can guarantee showing the msgbox at the end of this sub AFTER the two ranges are refreshed, but it cannot refresh ALL the ranges at the same time(asynchronously)
   arrRngName = Array("rng1","rng2")
   For Each itm in arrRngName
        With ThisWorkbook.Connections("Query - " & itm).OLEDBConnection
            .BackgroundQuery = False
            .Refresh
        End With
   Next itm
   MsgBox "All the refreshes are done asynchronously" 'This is an example of the subsequent sentence
End Sub

excel

来源：https://stackoverflow.com/questions/72272581/how-to-refresh-certain-ranges-asynchronously-generated-by-a-query-of-power-query

1条答案

按热度按时间

ma8fv8wu1#

不幸的是，没有可以用OLEDBConnection对象捕获的事件，因此您需要设计自己的方法。
有两个简单的选择：

1.计算后

如果您的工作表中有一个表被Query修改，那么您可以处理AfterCalculate事件，如下所示：

在ThisWorkbook模块中 *

Option Explicit

Private WithEvents mApp As Application
Private mIsManualRefresh As Boolean
Public Sub AwaitManualRefreshComplete()
    mIsManualRefresh = True
    If mApp Is Nothing Then Set mApp = Application
End Sub

Private Sub mApp_AfterCalculate()
    If mIsManualRefresh Then
        mIsManualRefresh = False
        Debug.Print "Manual refresh complete (after calculate)."
    End If
End Sub

然后在模块中：*

Public Sub RunMe1()
    Dim arrRngName As Variant
    Dim itm As Variant
    Dim conn As WorkbookConnection
    
    arrRngName = Array("qryAges", "qryGender", "Merge")
    
    ThisWorkbook.AwaitManualRefreshComplete
    For Each itm In arrRngName
        Set conn = ThisWorkbook.Connections("Query - " & itm)
        With conn.OLEDBConnection
            .BackgroundQuery = True
            .Refresh
        End With
        Debug.Print "Refresh " & conn.Name
    Next
End Sub

2. OnTime

使用计时器测试OLEDBConnection上的Refreshing属性

在模块中：*

Public Sub RunMe2()
    Dim arrRngName As Variant
    Dim itm As Variant
    Dim conn As WorkbookConnection
    
    arrRngName = Array("qryAges", "qryGender", "Merge")
    For Each itm In arrRngName
        Set conn = ThisWorkbook.Connections("Query - " & itm)
        With conn.OLEDBConnection
            .BackgroundQuery = True
            .Refresh
        End With
        Debug.Print "Refresh " & conn.Name
    Next
    AwaitManualRefreshComplete arrRngName
End Sub

Public Sub AwaitManualRefreshComplete(Optional arr As Variant)
    Static arrRngName As Variant
    Dim itm As Variant
    Dim conn As WorkbookConnection
    Dim isRefreshComplete As Boolean
    
    If Not IsMissing(arr) Then
        arrRngName = arr
    End If
    
    isRefreshComplete = True
    For Each itm In arrRngName
        Set conn = ThisWorkbook.Connections("Query - " & itm)
        If conn.OLEDBConnection.Refreshing Then
            isRefreshComplete = False
        End If
    Next
    
    If Not isRefreshComplete Then
        Application.OnTime _
            EarliestTime:=Now + TimeSerial(0, 0, 1), _
            Procedure:="AwaitManualRefreshComplete"
        Exit Sub
    End If
    
    Debug.Print "Manual refresh complete (after time)."
End Sub

赞(0）回复(0）举报 2023-03-24

我来回答

如何使用VBA在Excel中异步刷新Power Query查询生成的某些Ranges？

1条答案

相关问题

热门标签

最新问答