Excel VBS/VBA Run macro on specified files

[belen373]

Hello, I have a VBScript (that means I don’t have excel open) that looks for a certain file (book1.xls in this case) in a folder I have specified, runs a macro from PERSONAL.XLSB and saves the file. Script works fine.

I want the script to run this macro on book1.xls, book2.xls, etc… Is there a way to do this without writing the same script for every file? I am using Excel 2010 and there are many files in my folder, I only need to run this macro on specific files.



Thank you

MY CODE:

Option Explicit

Dim xlApp, xlbook, xlApp2

Set xlApp = CreateObject ("Excel.Application")
Set xlBook = xlApp.Workbooks.Open ("C:\PATH TO MY FILES\book1", 0, False)
Set xlApp2 = xlApp.Workbooks.Open("C:\PATH TO Personal.xlsb FILE")

xlApp.DisplayAlerts = False
xlApp.Application.Visible = False

'Runs "XrefMacro1" Macro found in Personal.xlsb
xlApp.Run XlApp2.Name & "!XrefMacro1"

xlBook.Save

xlBook.Close
xlApp2.Close
xlApp.Quit

Set xlBook = Nothing
Set xlApp2 = Nothing
Set xlApp = Nothing

'WScript.Echo "Finished."
WScript.Quit

xlApp.DisplayAlerts = True

 

[sastoka]

Hi there

you might want to give a look at that.Search all workbooks in a folder | Get Digital Help - Microsoft Excel resource

Regards