Excel:VBA和refreshall结束消息? [英] Excel: VBA and refreshall ended message?
问题描述
这是Excel 2003.我想知道外部查询需要多长时间才能完成,然后使用该ET更新电子表格中的单元格.我有以下内容,但它不起作用,因为ET仅与启动刷新所需的时间一样长:
This is Excel 2003. I'd like to know how long it takes an external query to complete and then update a cell in my spreadsheet with that ET. I have the following, but it doesn't work because the ET is only as long as it takes to initiate the refresh:
Sub Refresh()
Dim StartTime, EndTime, ET
StartTime = Timer
ActiveWorkbook.RefreshAll
EndTime = Timer
ET = Format(EndTime - StartTime, "Fixed")
Range("H27").Value = ET
MsgBox (ET)
End Sub
因此,即使数据获取花费了10分钟之久,ET仍约为1秒.
So the ET is about 1 second, even though the data fetch takes a good 10 minutes.
一种简单的解决方法是将后台刷新设置为false,但这会阻塞整个应用程序,并使生活长期处于痛苦状态.
The easy way out is to set background refresh to false, but this blocks the whole application and makes life miserable for a long time.
我可以在VBA中捕获某种信号或异常,该信号或异常指示哦,后台刷新已完成;现在您可以停止计时器并计算ET"了.
Is there some kind of signal or exception that I can catch in VBA that indicates "oh, a background refresh is done; now you can stop your timer and calculate the ET"?
谢谢!
推荐答案
我想您需要使用AfterRefresh事件.
此处是一个论坛讨论,带有愉快的结局和示例.
I guess you need to use the AfterRefresh event.
Here is a forum discussion with a happy ending and examples.
仅出于链接独立性的目的,从引用的页面粘贴示例(您应该添加计时器存储和算术运算):
Pasting the example from the referred page, just for link independence (you should add your timer storage and arithmetic):
此代码在模块上进行:
Dim X As New Class1
Sub Initialize_It()
Application.DisplayAlerts = False
Application.ScreenUpdating = True
diropen = "C:\Desktop\"
Workbooks.Open diropen & "Test.xls" , UpdateLinks:=0
Set X.qt = Workbooks("Test.xls").Sheets("Sheet1").QueryTables(1)
ActiveWorkbook.RefreshAll
End Sub
此代码在类模块上运行:
Public WithEvents qt As QueryTable
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
' Declare variables.
Dim a As Integer
Dim My_Prompt As String
' Initialize prompt text for message box.
My_Prompt = "Data refreshed or canceled."
' Displays message box before refresh (or cancel) occurs.
MsgBox My_Prompt
ActiveWorkbook.Save
Workbooks("Test.xls").Close
End Sub
这篇关于Excel:VBA和refreshall结束消息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!