Run excel macro on multiple files
Solution 1
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.
Solution 2
Two potential solutions below,
- vbscript which can be run directly as a vbs file
- A vba solution to be run from within Excel (as per Tim Williams suggestion)
vbscript solution
Dim objFSO
Dim objFolder
Dim objFil
Dim objXl
Dim objWb
Dim objExcel
Set 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 If
Next
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
Loop
End Sub
Solution 3
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 = False
objExcel.Application.Save
objExcel.Application.Quit
Set 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.
Jonathan
Updated on June 24, 2022Comments
-
Jonathan over 1 year
I have an excel macro saved in a blank workbook and multiple data workbooks.
I currently open the macro file and each data file individually, running the macro on each one with a keyboard shortcut.
Is there a way to run the macro on all the data workbooks without opening them, either with
- a batch file,
- VBA/VBScript,
- powershell,
- or something similar?
-
Simone Pistecchia almost 10 yearsi robert, if i have 2 ore more PC, with the excel in the server, how can i do this?
-
Robert Ilbrink almost 10 yearsWhen each user creates their own instance of this single Excel executable, then you will get the "PERSONAL.xlsb is locked for editing". Ideally Microsoft should not have placed this file in the programs directory, but in the users directory. Not sure if you can configure Excel to read the personal.xlsb file from the users directory?