Excel 2010允许您在最快的设置下每分钟刷新一次外部数据源:
数据 -> 属性 -> 每隔多少时间刷新
如何使用最佳的vba不到一分钟就持续更新连接?理想情况下,我想为每个单独的连接设置刷新频率,但刷新所有连接也足够了。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
If Cancel = False Then Application.OnTime dTime, "RefreshIt", , False
On Error Goto 0
End Sub
Private Sub Workbook_Open()
Run "RefreshIt"
End Sub
Public dTime As Date
Sub RefreshIt()
Sheets(1).Range("A1").QueryTable.Refresh
dTime = Time + TimeValue("00:00:30")
Application.OnTime dTime, "RefreshIt"
End Sub
感谢Dave Hawley在以下论坛上的贡献 http://www.ozgrid.com/forum/showthread.php?t=24119