vba 在多个文件上运行excel宏

Run excel macro on multiple files

提问by Jonathan

I have an excel macro saved in a blank workbook and multiple data workbooks.

我有一个 excel 宏保存在一个空白工作簿和多个数据工作簿中。

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?

• 一个批处理文件，
• VBA/VBScript，
• 电源外壳，
• 或类似的东西？

回答by Robert Ilbrink

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.

一种方法是将您的宏添加到文件 PERSONAL.XLSB。每次启动 Excel 时，都会在后台加载此文件。最初 PERSONAL.XLSB 文件将不存在。要自动创建此文件，只需开始录制“虚拟”宏（使用电子表格左下角的录制按钮）并选择“个人宏工作簿”将其存储。录制宏后，您可以打开 VBA使用 [Alt]+[F10] 编辑器，您将看到带有“虚拟”宏的 PERSONAL.XLSB 文件。我使用此文件来存储始终可用的大量通用宏。我已将这些宏添加到我自己的菜单功能区中。此通用宏文件的一个缺点是，如果您启动多个 Excel 实例，您将收到一条错误消息，指出 PERSONAL. XLSB 文件已被 Excel 实例 Nr 使用。1. 只要你此时不添加新的宏就没有问题。

回答by brettdj

Two potential solutions below,

下面两个可能的解决方案，

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

1. vbscript可以直接作为 vbs 文件运行
2. 甲VBA溶液从Excel内运行（按照添威廉斯建议）

vbscriptsolution

脚本解决方案

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

vbasolution

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

回答by BSadar

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:

我刚刚偶然发现了你的帖子，也许很晚了，但对于所有未来的搜索。可以通过创建 .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

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

保存文件如下（“你的文件名”.vbs）

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

通过双击（打开）保存的.vbs文件，它将启动您的宏，而您根本无需打开 Excel 文件。

Hope this helps.

希望这可以帮助。

回答by Cuchulainn

You could keep the macro in your personal.xls, or a masterfile, and loop through the workbooks with vba, and activate them before running your macro. As far as I know, you still have to open them with vba though.

您可以将宏保存在您的个人.xls 或主文件中，并使用 vba 循环浏览工作簿，并在运行宏之前激活它们。据我所知，你仍然需要用 vba 打开它们。

You could 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

回答by Bruno Leite

Other 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