Geek asks and answers

Run excel macro for multiple files

I have an excel macro saved in an empty workbook and several data books.

I am currently opening a macro file and each data file separately, running a macro on each of them using a keyboard shortcut.

Is there a way to run a macro in all data books without opening them, or using

  • batch file
  • VBA / VBScript,
  • PowerShell
  • or something similar?
+3
source share
5 answers

- PERSONAL.XLSB. Excel. PERSONAL.XLSB . , "dummy" ( ) " ", . VBA [Alt] + [F10], PERSONAL.XLSB "dummy". , . . , Excel , PERSONAL.XLSB Excel Nr. 1. , .

+3

,

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

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
+2

, , , . , .vbs. :

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

( " ".vbs)

() .vbs , excel .

, .

+1

You can save the macro to your personal.xls file or master file and scroll through books with vba and activate them before running your macro. As far as I know, you still have to open them with vba.

You can use something like this:

sub LoopFiles
  Dim sFiles(1 to 10) as string  'You could also read this from a range in a masterfile
      sFiles(1) = "Filename1.xls"
         .
         .
      sFiles(10) = "Filename10.xls"
  Dim wb as Workbook
  Dim iCount as integer
     iCount = ubound(sFiles)
  Dim iCount2 as integer

  For iCount2 = 1 to iCount
     Workbooks(sFiles(iCount2)).open
     Workbooks(sFiles(iCount2)).activate
     Call YourMacro
     Workbooks(sFiles(iCount2)).close
  next iCount2
end sub
0
source

Another way,

Sub LoopAllWorkbooksOpened()

Dim wb As Workbook

For Each wb In Application.Workbooks
    Call YourMacroWithWorkbookParam(wb)
Next wb

End Sub

Sub YourMacroWithWorkbookParam(wb As Workbook)
    MsgBox wb.FullName
End Sub
0
source

More articles:


All Articles