如何通过使用VBA?

发布于 2025-01-29 13:21:44 字数 1773 浏览 3 评论 0原文

堆栈溢出中有一些帖子指示如何:

  • 刷新所有查询所有查询异步,
  • 刷新一些查询,一个一个一个一个一个一个一个(即,不同步)

如何刷新某些范围(例如,给定这些范围的一系列名称)在检测到这些异步刷新后,通过excel中的power查询查询和在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

There are posts in Stack Overflow that indicate how to:

  • Refresh all queries asynchronously
  • Refresh some queries one by one (i.e. not asynchronously)

How do I refresh certain ranges (for example, given an array of those ranges' name) asynchronously generated by a query of Power Query in Excel with VBA and execute subsequent sentences in VBA after detecting those asynchronous refreshes are done.

Ways I've tried include:

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

紅太極 2025-02-05 13:21:44

不幸的是,没有任何事件可以用OLEDBConnection对象捕获,因此您需要设计自己的方法。

有两个简单的选择:

1。 allcculate

如果您的工作表上有一个由查询修改的表,则可以处理aftercalculate事件,如下所示:

在thisworkbook Bookbook module

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

< em>,然后在模块中:

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上的属性

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

Unfortunately, there are no events that can be trapped with an OLEDBConnection object, so you will need to devise your own method.

There are two easy options:

1. AfterCalculate

If you have a table on your sheet that is modified by the Query, then you can handle the AfterCalculate event, as shown below:

In ThisWorkbook module

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

and then in a Module:

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

Use a timer to test the Refreshing property on your OLEDBConnection

In a Module:

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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文