0

The goal is to have Visual Basic script refreshing the external data of an xlsm file.

The script runs without errors, but afterwards, nothing has happened to the xlsm file. It seems like the script continuous before all the data has been refreshed.

I've tried a few things already.

  • Disabling background query update
  • Application.CalculateUntilAsyncQueriesDone
  • WScript.Sleep ... to force the script to wait until refresh is finished, at the moment I'm trying with 30 minutes, shouldn't need more than 15 minutes though

Here is the script's content:

Option Explicit

Dim xlApp, xlBook

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("name_of_my_file.xlsm")

xlBook.RefreshAll
WScript.Sleep 1800000

xlBook.Save
xlBook.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp = Nothing

WScript.Echo "Finished test v2, i.e. refreshing data"
WScript.Quit
G H Hardy second acc
  • 1
  • Runs fine on my device (with the Sleep time much reduced!) ... on your device, is the 'last modified' time of the file (ie the Workbook) updated following the `.Save`? Also did you check out the answers to this [SO question](https://stackoverflow.com/q/22083668/11318818) – JohnM Jun 26 '23 at 11:38
  • If you disable Background query then you don't need the sleep - make sure *all* of your queries have this set though. – CHill60 Jun 26 '23 at 12:10
  • It turned out to be my reference to the file that found some other version of the file. Once I replaced the path with a full absolute reference, things worked. – G H Hardy second acc Jun 27 '23 at 12:24

0 Answers0