Print all workbooks in a folder using VBA in Microsoft Excel

In this article, we will create a macro to print all the Excel files within a folder.

We have some Excel files within a folder which we want to print. All of them have the same file extension, “.xlsx”

Code explanation

Dir(TargetFolder & FileFilter)

The above code is used to get file name of the first file within the folder path.

Workbooks.Open TargetFolder & FileName

The above code is used to open the defined workbook.

ActiveWorkbook.PrintOut

The above code is used to print the active workbook.

Please follow below for the code

Option Explicit

Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)

'Declaring variable
Dim FileName As String

'Disabling screen updates
Application.ScreenUpdating = False

'Adding path separator in the end of target folder name
If Right(TargetFolder, 1) <> "\" Then
    TargetFolder = TargetFolder & "\"
End If

'Assigning default path to file filter
If FileFilter = "" Then FileFilter = "*.xls"

'Get the file name of first file in the folder
FileName = Dir(TargetFolder & FileFilter)

While Len(FileName) > 0

    If FileName <> ThisWorkbook.Name Then
        
        'Open workbook
        Workbooks.Open TargetFolder & FileName
        
        'Prints all sheets in the workbook
        ActiveWorkbook.PrintOut
        
        'Close the workbook without saving any changes
        ActiveWorkbook.Close False
    
    End If
    
    'Get file name of next file in the folder
    FileName = Dir

Wend


End Sub

Sub CallingProcedure()

'Declaring variables
Dim FolderPath, FileName As String

'Getting values from textbox on sheet1
FolderPath = Sheet1.TextBox1.Value
FileName = Sheet1.TextBox2.Value

'Calling PrintAllWorkbooksInFolder procedure
PrintAllWorkbooksInFolder FolderPath, FileName
    
End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com