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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
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,047
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,047
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,108
Messages
5,570,243
Members
412,312
Latest member
linister
Top