How to set a delay in VB Script to let Excel update data

11,755

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
Share:
11,755
r-q
Author by

r-q

Updated on June 05, 2022

Comments

  • r-q
    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:

    1. Removing the "WScript.Sleep 1000 * 60 * 5" line from the VB script altogether
    2. In Excel, clearing "Enable Background Refresh" checkbox in Properties for each connection
    3. 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!