Printing sheet array in workbook, looping in file

[Mathexcel]

Hi,
I need to do a print specific sheets in 30 workbooks. The sheet names remain the same. I save my files in a folder and want to execute my printing from a document with a printing macro that would allow me to select the folder (or documents) and print the sheets.

I have tried the following code but without success. The code jumps to then MsgBox without printing anything. Could anyone advise on what I am doing wrong?

Sub LoopAllExcelFilesInFolderForPrinting()


Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog


'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & ""
    End With


'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.xlsm*"


'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)


'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      
      DoEvents
           
        wb.Worksheets(Array("Comp", "Response", "Day of Week", "Segmentation at Glance", "Segmentation Ranking", "Segmentation DOW YTD", "Segmentation Response", "Summary")).PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
        wb.Close
      
      DoEvents


    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Printing done"


End Sub

Thank you in advance.

 

[John_w]

I've tweaked your macro.

Sub LoopAllExcelFilesInFolderForPrinting()

    Dim wb As Workbook
    Dim myPath As String
    Dim myFile As String
    Dim myExtension As String
    Dim FldrPicker As FileDialog
    
    'Retrieve Target Folder Path From User
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    
    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show Then
            myPath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    
    myPath = myPath & "\"
    
    'Target File Extension (must include wildcard "*")
    myExtension = "*.xlsm*"
    
    'Target Path with Ending Extention
    myFile = Dir(myPath & myExtension)
    
    'Loop through each Excel file in folder
    Do While myFile <> ""
        'Set variable equal to opened workbook
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
        
        DoEvents
        
        wb.Worksheets(Array("Comp", "Response", "Day of Week", "Segmentation at Glance", "Segmentation Ranking", "Segmentation DOW YTD", "Segmentation Response", "Summary")).PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        
        wb.Close
        
        DoEvents
        
        'Get next file name
        myFile = Dir
    Loop
    
    'Message Box when tasks are completed
    MsgBox "Printing done"
       
End Sub

 

[Mathexcel]

Hum the macro sends me back directly to my MsgBox without doing anything.

It seems like the documents are not even opened...

 

[John_w]

This line:

  myExtension = "*.xlsm*"

means the code is looking for macro-enabled workbooks (.xlsm files). Are the workbooks .xlsm files?

If not, change that line to:

  myExtension = "*.xls*"

and then it should look for and open any type of Excel workbook.

 

[Mathexcel]

Dam it of course, how didn't I see that! Thank you so much!