Print all workbooks in a folder
2000-02-04 Printing 0 77
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