printing all workbooks in a directory and all sheets in each

[Kyle775]

i need to print all workbooks in a directory and i need all the sheets in each book to be included in the print and to print each sheet with its own pagnation (not 1 - n, where n is the total pages fro all work sheets, 1 - n where n is the total number of pages for the sheet that is being printed.) i have tried doing this with the windows explorer and it misses some workbooks and only prints the active sheet. ive looked around and there are some solutions to this but i havent been able to get them to work. can someone point me in the right direction... thanks a lot!

 

[Kyle775]

i get the error at this line

 .LookIn = GetFolder

well thats where the debug points to

 

[Norie]

Kyle

Under what circumstances do you get the error?

 

[dantb]

Hello Norie: Nice code by the way. The error occurs only when you have the (Browse for Folder) box open and either select Cancel or close the box out using the X in the upper right hand corner.

 

[Norie]

dantb

That's exactly what I thought.

What's needed then is a test for an empty string, I think that's what's returned when you hit cancel or close.

I'll take a look at it.

 

[Norie]

Try this.

Sub PrintAllWS()
Dim wb As Workbook
Dim ws As Worksheet
Dim MyFolder
Dim I As Long

    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    MyFolder = GetFolder
    If MyFolder = vbNullString Then
        MsgBox "No folder selected, exiting code.", vbCritical
        Exit Sub
    End If
    
    With Application.FileSearch
        .NewSearch
        .LookIn = MyFolder
        .FileType = msoFileTypeExcelWorkbooks
        .Execute

        For I = 1 To .FoundFiles.Count
            Set wb = Workbooks.Open(.FoundFiles(I))
            For Each ws In wb.Worksheets
                 ws.PrintOut
            Next ws
            wb.Close
        Next I
    End With
    
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Private Function GetFolder() As String
'common UDF for getting a folder name
    Dim ff As Object
    Set ff = CreateObject("Shell.Application"). _
             BrowseForFolder(0, "Please select a folder", 0, "c:\\")
    If Not ff Is Nothing Then
        GetFolder = ff.Items.Item.path
    Else
        GetFolder = vbNullString
    End If
End Function

 

[dantb]

Works Perfect Norie, I see where this code can be very useful. I replaced the Msgbox with a DisplayAlerts = False just to save click if canceled. Thanks for the learning experience. Dan

 

[Kyle775]

this is very useful for me, thanks everyone...

im glad the thread was helpful to more than one person!