在多个excel工作簿上运行多个宏 - vba [英] running multiple macros in order across multiple excel workbooks - vba

问题描述

我需要依次运行6个宏在工作簿中的每个工作表上，然后移动到下一个工作簿（所有工作簿都在桌面上的相同文件夹中）

）在所有的表单上顺序运行宏，但是我试图让所有的工作簿都可以运行麻烦。

  Sub RUN_FILL（）
 Dim sh As Worksheet 
 
对于每个sh在ThisWorkbook.Worksheets 
 sh.Activate 
 
调用macro_1 
调用macro_2 
调用macro_3 
调用macro_4 
调用macro_5 
调用macro_6 
 
下一步sh 
 End Sub 
  

任何想法我该怎么做？

解决方案

p>我没有你的宏，所以我创建了虚拟宏，向Immedi输出一些值每个工作簿的每张工作表（除了包含宏的工作簿除外）的窗口。

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

我希望这足以让你开始。询问是否有什么不清楚。

  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 
 
如果Workbooks.Count> 1然后
'如果在这样一个宏的开始有多个工作簿
'打开，很容易陷入困境。避免这个问题。 
在运行此宏之前调用MsgBox（请关闭所有其他工作簿& _ 
，vbOKOnly）
退出子
如果
 
 Application.ScreenUpdating = False 
 
设置WBookCtrl = ActiveWorkbook 
 
'假设要处理的所有工作簿位于
与包含此宏的工作簿相同的文件夹中。 
 PathCrnt = WBookCtrl.Path 
 
'如果需要，在路径末尾添加斜杠。 
如果右（PathCrnt，1）<> \然后
 PathCrnt = PathCrnt& \
 End If 
 
 FileNameCrnt = Dir $（PathCrnt&* .xl *）
 
尽管FileNameCrnt& 
 
如果FileNameCrnt<> WBookCtrl.Name然后
'考虑除包含这个宏的所有工作簿
设置WBookOther = Workbooks.Open（PathCrnt& FileNameCrnt）
 
对于InxWSheet = 1 To WBookOther.Worksheets调用macro_2（WBookOther，WSheetNameOtherCrnt）
调用宏_2（WBookOther，WSheetNameOtherCrnt）
调用macro_3（WBookOther（））
 WSheetNameOtherCrnt = WBookOther.Worksheets（InxWSheet）.Name 
 
 ，WSheetNameOtherCrnt）
调用macro_4（WBookOther，WSheetNameOtherCrnt）
调用macro_5（WBookOther，WSheetNameOtherCrnt）
调用macro_6（WBookOther，WSheetNameOtherCrnt）
下一个
 WBookOther.Close SaveChanges： = False 
如果
 FileNameCrnt = Dir $（）
循环
 
 Application.ScreenUpdating = True 
 
 End Sub 
 Sub macro_1（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
带有WBookOther 
带.Worksheets（WSheetNameOtherCrnt）
 Debug.Print1& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cells（1，1）.Value 
 End with 
 End with 
 
 End Sub 
 Sub macro_2（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
带有WBookOther 
带有.Worksheets（WSheetNameOtherCrnt）
 Debug.Print2& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cells（1，1）.Value 
 End with 
 End with 
 
 End Sub 
 Sub macro_3（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
带有WBookOther 
带有.Worksheets（WSheetNameOtherCrnt）
 Debug.Print3& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cell（1，1）.Value 
 End with 
 End with 
 
 End Sub 
 Sub macro_4（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
带有WBookOther 
带有.Worksheets（WSheetNameOtherCrnt）
 Debug.Print4& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cells（1，1）.Value 
 End with 
 End with 
 
 End Sub 
 Sub macro_5（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
带有WBookOther 
带有.Worksheets（WSheetNameOtherCrnt）
 Debug.Print5& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cells（1，1）.Value 
 End with 
 End with 
 
 End Sub 
 Sub macro_6（WBookOther As Workbook，WSheetNameOtherCrnt As String）
 
与WBookOther 
与.Worksheets（WSheetNameOtherCrnt）
 Debug.Print6& WBookOther.Name& & _ 
 WSheetNameOtherCrnt& & .Cells（1，1）.Value 
 End With 
 End With 
 
 End Sub 
  

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

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)

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 ?

解决方案

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.

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

这篇关于在多个excel工作簿上运行多个宏 - vba的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！