如何通过使用VBA?
堆栈溢出中有一些帖子指示如何:
- 刷新所有查询所有查询异步,
- 刷新一些查询,一个一个一个一个一个一个一个(即,不同步)
如何刷新某些范围(例如,给定这些范围的一系列名称)在检测到这些异步刷新后,通过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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
不幸的是,没有任何事件可以用
OLEDBConnection
对象捕获,因此您需要设计自己的方法。有两个简单的选择:
1。 allcculate
如果您的工作表上有一个由查询修改的表,则可以处理
aftercalculate
事件,如下所示:在thisworkbook Bookbook module
< em>,然后在模块中:
2。 Ontime
使用计时器测试
OLEDBConnection上的属性
:
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
and then in a Module:
2. OnTime
Use a timer to test the
Refreshing
property on yourOLEDBConnection
In a Module: