How to set a delay in VB Script to let Excel update data
Solution 1
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?
Solution 2
For Excel-VBA you can use any one of the below to pause the code.
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))
sample code (will pause for 10 seconds)
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
r-q
Updated on June 05, 2022Comments
-
r-q over 1 year
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!