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

Answer 2

I suppose you can do something like this.

1. Find all the excel file in the given folder
2. Select Each Excel file and activate
3. call refreshdata function

code snippet to support this

Sub RefreshAllExcelInFolder()
    Dim fso
    Dim ObjFolder
    Dim ObjFiles
    Dim ObjFile
    Dim objExcel

    'Creating File System Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Getting the Folder Object
    Set ObjFolder = fso.GetFolder("<<C:folder path>>")

    'Getting the list of Files
    Set ObjFiles = ObjFolder.Files
        'On Error Resume Next
        For Each ObjFile In ObjFiles
            If LCase(Right(ObjFile.Name, 5)) = ".xlsx" Or LCase(Right(ObjFile.Name, 4)) = ".xls" Then
                Workbooks.Open(ObjFile).Activate
                RefreshAllData
            End If
        Next
End Sub

Sub RefreshAllData()
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    MsgBox ("Going back In")
End Sub

Hope this will help

Answer 3

If you don't change the macro at all you will have to keep reloading excel, then open the macro workbook followed by the target workbook and then run the macro. If you can remove:

Application.quit

then you can at least keep excel open and just keep opening target workbooks before running the macro each time. Seems to me it'd be simpler to put the macro code into the vbs file though