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”

ArrowFilesInFolder

ArrowMain

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube