VBScript to refresh all excel files in a specific folder

Question

I have some VBscript that should go out to a specific folder, open all .xlsm files within that folder, refresh all, save and close the files. This script is kicked off by windows task scheduler each morning at 4 a.m.

Currently, this script opens the first .xlsm file listed, refreshes, but does not save, then closes. no other files in the folder are opened.

This is the code i currently have:

Set fso = CreateObject("Scripting.FileSystemObject")
Set xl  = CreateObject("Excel.Application")
xl.Visible = True

For Each f In fso.GetFolder("[file path to folder i want files updated]").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsm" Then
    Set wb = xl.Workbooks.Open(f.Path)
    wb.RefreshAll
    WScript.Sleep 5*60*1000
    wb.Save
    wb.Close
  End If
Next

xl.Quit

Thank you for any suggestions.

How can you be sure about the files not being saved? The code looks ok to me. Have you tried to debug and see what is going on? — Pankaj Jaju
The refresh is bringing in a new line of data into a table each day. I know the files is not saving because, the data is not updated when i open the file, and the date modified is not changed. I am newer to VBscript, i have not ran a debugger. @PankajJaju — VbALearner

user12228588 user12228588 · Accepted Answer · 2019-10-16T19:01:38

Just change the setting of your excel to dont run in the backgrond when refreshing. This will solve your problem.

VBScript to refresh all excel files in a specific folder

1 Answers