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!

 

[Norie]

The only way I can think of doing this would be to open every workbook, print every worksheet and then close the workbook.

Sub PrintAllWS()
Const MyFolder = "C:\MyFolder\"
Dim I As Long
Dim wb As Workbook
Dim ws As Worksheet
        
    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
     
End Sub

 

[Kyle775]

this is a good idea, however if i need to print 5 worksheets from 62 workbooks? i think my computer will start smoking... is there a way to keep excel from actually opening up... the books? isnt there some sort of screen update property or method that could be shutoff??
sorry if im asking for too much! i spent way too much time opening and printing and closing a collating sheets... windows fails to print some so i found that out after i had pressed print had to go through 5 inches of pages to find out whats missing etc... really wasted a lot of time...

 

[Norie]

Forgot about that.

Try this

Sub PrintAllWS() 
Const MyFolder = "C:\MyFolder\" 
Dim I As Long 
Dim wb As Workbook 
Dim ws As Worksheet 

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

    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

This also disables events, in case any of the workbooks have code that runs when they are opened.

 

[Kyle775]

thanks that seems to work really well... im going to try it later this month on a large set of books.... thanks so much!@!

 

[Kyle775]

adding a prompt for the directory?

i just thought about this.....
is there anyway to add a prompt for the directory to print?? maybe even with a browse... button??

dont want to have to edit the code each time...

 

[Norie]

You could add a simple inputbox, but then that would rely on the user entering the folder correctly.

I'm sure there's code on the forum that will call up some sort of browser to do that.

 

[vane0326]

Will this work ?

Sub TestFile1()
    Dim I As Long
    Dim wb As Workbook
    Application.ScreenUpdating = False
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\Test"
        .SearchSubFolders = False
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
            For I = 1 To .FoundFiles.Count
             Set wb = Workbooks.Open(.FoundFiles(I))
             wb.PrintOut
             wb.Close False
            Next I
        End If
    End With
    Application.ScreenUpdating = True
End Sub

OR

Sub TestFile2()
    Dim i As Long
    Dim WB As Workbook
    Application.ScreenUpdating = False
z = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
For X = 1 To UBound(z)
Set WB = Workbooks.Open(z(X))
             WB.PrintOut
             WB.Close False
            Next X
    Application.ScreenUpdating = True
End Sub

 

[Kyle775]

without trying the code.... does it require me to choose a file? i want to choose an entire directory...

i cant find much else on the forum about adding a browse prompt.... only something about adding code that will allow browsing for a picture and inserting it... not what i need... and i cant modify the code... im not that bright.

 

[Kyle775]

ive tried this but get some sort of mismatch error

Sub PrintAllWS()
MyFolder = Application.GetOpenFilename(FileFilter:="Excel files (*.xls), *.xls", MultiSelect:=True)
Dim i As Long
Dim WB As Workbook
Dim ws As Worksheet

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

    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