Run excel macro on multiple files

One way to do this is to add your macro's to the file PERSONAL.XLSB. This file will be loaded in the background every time you start Excel. Initially the PERSONAL.XLSB file will NOT be there.To automatically create this file, just start recording a "dummy" macro (with the record button on the left-bottom of a spreadsheet) and select "Personal Macro Workbook" to store it in. After recording your macro, you can open the VBA editor with [Alt]+[F10] and you will see the PERSONAL.XLSB file with the "dummy" macro.I use this file to store loads of general macro's which are always available. I have added these macro's to my own menu ribbon.One disadvantage of this common macro file is that if you launch more than one instance of Excel, you will get an error message that the PERSONAL.XLSB file is already in use by Excel instance Nr. 1. This is no problem as long as you do not add new macro's at this moment.

Two potential solutions below,

1. vbscript which can be run directly as a vbs file
2. A vba solution to be run from within Excel (as per Tim Williams suggestion)

vbscript solution

Dim objFSODim objFolderDim objFilDim objXlDim objWbDim objExcelSet objExcel = CreateObject("Excel.Application")Set objFSO = CreateObject("scripting.filesystemobject")Set objFolder = objFSO.getfolder("c:\temp")For Each objFil In objFolder.Files    If InStr(objFil.Type, "Excel") > 0 Then        Set Wb = objExcel.Workbooks.Open(objFil.Path)        wscript.echo Wb.name        Wb.Close False    End IfNext

vba solution

Sub OpenFilesVBA()    Dim Wb As Workbook    Dim strFolder As String    Dim strFil As String    strFolder = "c:\Temp"    strFil = Dir(strFolder & "\*.xls*")    Do While strFil <> vbNullString        Set Wb = Workbooks.Open(strFolder & "\" & strFil)        Wb.Close False        strFil = Dir    LoopEnd Sub

I sort of stumbled across your post just now, maybe very late, but for all future searches.It is possible to launch your Macro by creating a .vbs file. To do this, open notepad and add the following:

objExcel = CreateObject("Excel.Application")objExcel.Application.Run <insert macro workbook file path, module and macro name here>objExcel.DisplayAlerts = FalseobjExcel.Application.SaveobjExcel.Application.QuitSet objExcel = Nothing

save the file as follows ("your filename".vbs)

By double clicking (opening) the saved .vbs file, it will launch your macro without you having to open your excel file at all.

Hope this helps.