vba 如何在 VB Script 中设置延迟让 Excel 更新数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16717438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set a delay in VB Script to let Excel update data
提问by r-q
I have an Excel file that automatically loads the data from an external database (via a Data Connection to an Accesss database that is, in turn, linked to a Sharepoint table). I need to automate the process of:
我有一个 Excel 文件，它会自动从外部数据库加载数据（通过数据连接到 Accesss 数据库，该数据库又链接到 Sharepoint 表）。我需要自动化以下过程：
- opening the Excel file
- waiting for the data to refresh
- running a macro (in this file)
- closing the file
- 打开 Excel 文件
- 等待数据刷新
- 运行宏（在这个文件中）
- 关闭文件
The script that I have is:
我拥有的脚本是：
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("excel spreadsheet.xlsm")
WScript.Sleep 1000 * 60 * 5
objExcel.Run "macro_name"
objWorkbook.close False
objExcel.Application.Quit
However, no matter what delay I set in WScript.sleep, it does not update the data; instead, it runs the macro on the data already in the spreadsheet. Could anyone help, please!
但是，无论我在 WScript.sleep 中设置什么延迟，它都不会更新数据；相反，它对电子表格中已有的数据运行宏。有没有人可以帮忙，拜托！
SOLUTION
解决方案
In the end (and with the benefit of a few days' live testing), the following seems to be the cleanest and most efficient:
最后（并得益于几天的现场测试），以下似乎是最干净和最有效的：
- Removing the "WScript.Sleep 1000 * 60 * 5" line from the VB script altogether
- In Excel, clearing "Enable Background Refresh" checkbox in Properties for each connection
Adding the following line to the macro before the main code
ActiveWorkbook.RefreshAll
- 从 VB 脚本中完全删除“WScript.Sleep 1000 * 60 * 5”行
- 在 Excel 中，清除每个连接的属性中的“启用后台刷新”复选框
将以下行添加到主代码之前的宏中
ActiveWorkbook.RefreshAll
Many thanks to Philip and Santosh for the answers below pointing me in the right direction!
非常感谢 Philip 和 Santosh 的回答，为我指明了正确的方向！
采纳答案by Our Man in Bananas
回答by Santosh
For Excel-VBA you can use any one of the below to pause the code.
对于 Excel-VBA，您可以使用以下任何一种来暂停代码。
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
OR
或者
Private Sub delay(seconds As Long)
Dim endTime As Date
endTime = DateAdd("s", seconds, Now())
Do While Now() < endTime
DoEvents
Loop
End Sub
or
或者
Application.Wait (Now() + TimeSerial(0, 0, 5))
示例代码（将暂停 10 秒）
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("excel spreadsheet.xlsm")
objExcel.Wait (Now + TimeSerial(0, 0,10))
objExcel.Run "macro_name"
objWorkbook.close False
objExcel.Application.Quit
