» Print all workbooks in a folder using VBA in Microsoft Excel
VBA macro tip contributed by
Erlandsen
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
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
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Monte Carlo Methods in Finance
