These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Print all workbooks in a folder

With the macros below you can print all workbooks in a selected folder. You have more control with what is printed than you have if you do this from Windows Explorer.

Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)
' prints all workbooks in a folder that matches the FileFilter
' example: PrintAllWorkbooksInFolder "C:\FolderName", "*.xls"
' example: PrintAllWorkbooksInFolder "C:\FolderName", "Bud*.xls"
Dim fn As String, sht As Variant
    Application.ScreenUpdating = False
    If Right(TargetFolder, 1) <> Application.PathSeparator Then
        TargetFolder = TargetFolder & Application.PathSeparator
    End If
    If FileFilter = "" Then FileFilter = "*.xls"
    fn = Dir(TargetFolder & FileFilter) ' the first file name in the folder
    While Len(fn) > 0
        If fn <> ThisWorkbook.Name Then
            Application.StatusBar = "Printing " & fn & "..."
            Workbooks.Open TargetFolder & fn
            ActiveWorkbook.PrintOut ' prints all sheets in the workbook

            ' or print each separate sheet
'            For Each sht In ActiveWorkbook.Sheets
'                sht.PrintOut
'            Next sht

            ' print a specific sheet or chart
'            Worksheets(1).PrintOut ' prints the first worksheet in the workbook
'            Charts(2).PrintOut ' prints the second chart sheet in the workbook

            ' print all sheets of a specific sheet type
'            For Each sht In ActiveWorkbook.Sheets
'                Debug.Print ActiveWorkbook.Name & " " & _
                    sht.Name & " is of type " & TypeName(sht) ' can be removed...
'                If TypeName(sht) = "Worksheet" Then
'                    sht.PrintOut ' print worksheets
'                End If
'                If TypeName(sht) = "Chart" Then
'                    sht.PrintOut ' print charts
'                End If
'            Next sht
            ActiveWorkbook.Close False 
            ' close the workbook without saving any changes
        End If
        fn = Dir ' the next file name in the folder
    Wend
    Application.StatusBar = False
End Sub

Document last updated 2000-02-04 12:50:09