How can I use VBScript to effectively refresh the external data of several Excel spreadsheets?

Question

I have a macro in one XLSM workbook's module that refreshes all the external data, then saves and closes that workbook.

ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Quit

I use a VBScript file to run that macro as part of a scheduled task

objExcel.Workbooks.Open(fname)
objExcel.Visible = True
On error resume next
objExcel.Run "RefreshAllData"

Question: How can I reuse the existing macro in the existing workbook to refresh all the data of multiple other workbooks? (ie. I'm looking for the necessary modifications to the VBScript file, I want to minimise changes to the macro itself. The filenames will be contained in the VBScript file) TIA.

Answer 1

I'd recommend against re-using a trivial macro like that. Instead incorporate the refresh functionality in the VBScript:

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

For Each f In fso.GetFolder("C:\some\folder").Files
  If LCase(fso.GetExtensionName(f.Name)) = "xlsx" Then
    Set wb = xl.Workbooks.Open(f.Path)
    wb.RefreshAll
    wb.Save
    wb.Close
  End If
Next

xl.Quit