printing all workbooks in a directory and all sheets in each

Kyle775

Board Regular
Joined
Feb 16, 2005
Messages
230
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
The only way I can think of doing this would be to open every workbook, print every worksheet and then close the workbook.
Code:
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

Board Regular
Joined
Feb 16, 2005
Messages
230
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
:oops: Forgot about that.

Try this
Code:
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

Board Regular
Joined
Feb 16, 2005
Messages
230

ADVERTISEMENT

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

Board Regular
Joined
Feb 16, 2005
Messages
230
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 29, 2004
Messages
819
Will this work ?

Code:
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


Code:
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

Board Regular
Joined
Feb 16, 2005
Messages
230
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

Board Regular
Joined
Feb 16, 2005
Messages
230
ive tried this but get some sort of mismatch error

Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,545
Messages
5,838,004
Members
430,525
Latest member
WHall

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top