如何在VB Script中设置延迟，让Excel更新数据 [英] How to set a delay in VB Script to let Excel update data

问题描述

我有一个Excel文件，从外部数据库（通过数据连接到Accesss数据库，反过来，链接到Sharepoint表）自动加载数据。我需要自动执行以下过程：

• 打开Excel文件


• 等待数据刷新


• 运行宏（在此文件中）


• 关闭文件

我有的脚本是：

 设置objExcel = CreateObject（Excel.Application）
 Set objWorkbook = objExcel.Workbooks.Open（excel spreadsheet.xlsm）
 WScript.Sleep 1000 * 60 * 5 
 objExcel.Runmacro_name
 objWorkbook.close False $但是，无论在WScript.sleep中设置什么样的延迟，我们都可以使用这种方法来创建一个新的窗口。它不更新数据;相反，它对电子表格中已有的数据运行宏。请任何人帮助！
 
 
 
 
 
 
解决方案
 
 
 
 
 
 
删除
 
 在Excel中，清除每个连接的属性中的启用后台刷新复选框 
 
 
将以下行添加到主代码之前的宏
：
 
 
 ActiveWorkbook.RefreshAll 
 
 
 
非常感谢Philip和Santosh为我的答案指出正确的方向！
解决方案
在您的工作簿中，您需要在运行代码之前刷新QueryTables中的数据。
 
 
 
为什么不调用刷新方法在查询表上，在您的excel vba宏开头？
 
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:


opening the Excel file
waiting for the data to refresh 
running a macro (in this file)
closing the file


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!



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
Many thanks to Philip and Santosh for the answers below pointing me in the right direction!
解决方案
in your workbook you need to refresh the data in your QueryTables before you run you code.

Why not call the Refresh method on the querytable, at the beginning of your excel vba macro?

                        
这篇关于如何在VB Script中设置延迟，让Excel更新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！