DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

How To Print all workbooks in a folder using VBA in Excel?

#toprintallworkbooksi #vbamacros

In this article, you will learn how to print all Workbooks in a folder using VBA in Excel. Let’s see them below!! Get the official version of MS Excel from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Example

  • Firstly, you have some Excel files within a folder which we want to print.

All Excel Files

  • In the Excel Worksheet, you have to go to the Developer Tab.
  • Then, you need to ** ** select the Visual Basic option under the Code section.

Select Visual Basic
Select Visual Basic

  • Now, you have to copy and paste the code given below. 

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
Enter fullscreen mode Exit fullscreen mode
  • You need to save the code by selecting it and then close the window.

Save the Code

  • Again, you have to go to the Excel Spreadsheet , and click on the Developer Tab.
  • You need to choose the Macros option in the Code section.

Choose Macro option
Choose Macro option

  • Then, you have to make sure that your macro name is selected and click the *Run * button.

Run the Code

  • Now, you need to specify folder path and specify file name in the Excel.

Specify path and file name

  • Finally, you will print all Workbooks in a folder using VBA in Excel.

End of Era

We hope that this tutorial gives you guidelines on how to print all Workbooks in a folder using VBA in Excel. Please leave a comment in case of any queries, and don’t forget to mention your valuable suggestions as well. Thank you so much for Visiting Our Site!! Continue learning on Geek Excel!! *Read more on Excel Formulas *!!

Read Next:

Latest comments (0)