在多个excel工作簿中按顺序运行多个宏 - vba

running multiple macros in order across multiple excel workbooks - vba

提问by sam

I have multiple excel workbooks each representing a days data, each workbook has multiple sheets representing each event on the day..

我有多个 excel 工作簿，每个工作簿代表一天的数据，每个工作簿有多个工作表，代表当天的每个事件..

i need to run 6 macros in order across each sheet in a workbook and then move on to the next workbook (all the workbooks are in the same folder on the desktop)

我需要在工作簿中的每个工作表上按顺序运行 6 个宏，然后转到下一个工作簿（所有工作簿都在桌面上的同一个文件夹中）

at the moment im using this (below) to run the macros in order across all the sheets but im having trouble trying to get somthing to run across all of the workbooks

目前我正在使用这个（下面）在所有工作表中按顺序运行宏，但我在尝试让一些东西在所有工作簿中运行时遇到了麻烦

Sub RUN_FILL()
Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets
sh.Activate

Call macro_1
Call macro_2  
Call macro_3  
Call macro_4  
Call macro_5  
Call macro_6

Next sh
End Sub

any idea how i might do this ?

知道我怎么做吗？

回答by Tony Dallimore

I do not have your macros so I have created dummy macros that output some values to the Immediate window for every sheet of every workbook (except the workbook containing the macro).

我没有你的宏，所以我创建了虚拟宏，为每个工作簿的每个工作表（包含宏的工作簿除外）输出一些值到立即窗口。

You code appears to depend on the output macro activating each worksheet. This is bad practice. I pass the workbook and the worksheet name to the the macros. I output the value of cell A1 (.Cells(1, 1).Value) to show how it is done.

您的代码似乎取决于激活每个工作表的输出宏。这是不好的做法。我将工作簿和工作表名称传递给宏。我输出单元格 A1 ( .Cells(1, 1).Value)的值以显示它是如何完成的。

I hope this is enough to get you started. Ask if anything is unclear.

我希望这足以让你开始。问有没有不清楚的地方。

Option Explicit
Sub ControlCall()

  Dim FileNameCrnt As String
  Dim InxWSheet As Long
  Dim MsgErr As String
  Dim PathCrnt As String
  Dim RowReportCrnt As Long
  Dim WBookCtrl As Workbook
  Dim WBookOther As Workbook
  Dim WSheetNameOtherCrnt As String

  If Workbooks.Count > 1 Then
    ' It is easy to get into a muddle if there are multiple workbooks
    ' open at the start of a macro like this.  Avoid the problem.
    Call MsgBox("Please close all other workbooks " & _
                "before running this macro", vbOKOnly)
    Exit Sub
  End If

  Application.ScreenUpdating = False

  Set WBookCtrl = ActiveWorkbook

  ' Assume all the workbooks to be processed are in the
  ' same folder as the workbook containing this macro.
  PathCrnt = WBookCtrl.Path

  ' Add a slash at the end of the path if needed.
  If Right(PathCrnt, 1) <> "\" Then
    PathCrnt = PathCrnt & "\"
  End If

  FileNameCrnt = Dir$(PathCrnt & "*.xl*")

  Do While FileNameCrnt <> ""

    If FileNameCrnt <> WBookCtrl.Name Then
      ' Consider all workbooks except the one containing this macro
      Set WBookOther = Workbooks.Open(PathCrnt & FileNameCrnt)

      For InxWSheet = 1 To WBookOther.Worksheets.Count
        WSheetNameOtherCrnt = WBookOther.Worksheets(InxWSheet).Name

        Call macro_1(WBookOther, WSheetNameOtherCrnt)
        Call macro_2(WBookOther, WSheetNameOtherCrnt)
        Call macro_3(WBookOther, WSheetNameOtherCrnt)
        Call macro_4(WBookOther, WSheetNameOtherCrnt)
        Call macro_5(WBookOther, WSheetNameOtherCrnt)
        Call macro_6(WBookOther, WSheetNameOtherCrnt)
      Next
      WBookOther.Close SaveChanges:=False
    End If
 FileNameCrnt = Dir$()
Loop

Application.ScreenUpdating = True

End Sub
Sub macro_1(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "1 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_2(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "2 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_3(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "3 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_4(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "4 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_5(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "5 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub
Sub macro_6(WBookOther As Workbook, WSheetNameOtherCrnt As String)

  With WBookOther
    With .Worksheets(WSheetNameOtherCrnt)
      Debug.Print "6 " & WBookOther.Name & " " & _
                  WSheetNameOtherCrnt & " " & .Cells(1, 1).Value
    End With
  End With

End Sub

回答by chris neilsen

Pseudo code outline:

伪代码大纲：

For each file in folder  ' I'd use the FileSystemObject for this
    Set wb = Workbooks.Open file 
    For Each sh in wb.worksheets
        ....
    Next
    wb.save
    wb.close
Next