Run excel macro on multiple files

19,069

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,

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

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

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.

Share:
19,069
Jonathan
Author by

Jonathan

Updated on June 24, 2022

Comments

  • Jonathan
    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
    Simone Pistecchia almost 10 years
    i robert, if i have 2 ore more PC, with the excel in the server, how can i do this?
  • Robert Ilbrink
    Robert Ilbrink almost 10 years
    When 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?