VBA Trick: Combine Excel Sheets into One PDF

The need to merge multiple Excel spreadsheets into a single PDF document is common among professionals dealing with data management and analysis. This article will walk you through a step-by-step guide on how to accomplish this task efficiently using VBA (Visual Basic for Applications), Excel's powerful scripting language. The benefits of automating this process include saving time, reducing errors, and enhancing document management.

Why Combine Excel Sheets into One PDF?

Merging Excel sheets into one PDF can streamline various business processes:

• Document Organization: Consolidating multiple sheets into one PDF makes it easier to share and archive documents.
• Consistency: Ensure all data is presented in a standardized format.
• Automation: Automating repetitive tasks reduces manual labor and minimizes errors.

Before we dive into the VBA code, let's consider the prerequisites for this task:

• Microsoft Excel installed with a version that supports VBA scripting.
• Microsoft Edge PDF printer driver enabled, which is available in Windows 10 or later versions.

Creating the VBA Script

The following steps will guide you through the VBA script creation process:

1. Opening the VBA Editor

Open your Excel workbook, press ALT + F11 to launch the VBA editor. Here, you will write the code that will perform the PDF creation.

2. Inserting a New Module

Navigate to Insert > Module to add a new module where our macro will reside.

3. Writing the VBA Code

Below is the VBA code to combine sheets into one PDF:

Sub CombineSheetsIntoOnePDF()
    Dim ws As Worksheet
    Dim pdfPath As String
    Dim wkbk As Workbook
    Dim sht As Variant
    Dim pdfFileName As String
    Dim pdfDir As String
    
    Set wkbk = ActiveWorkbook
    
    pdfDir = wkbk.Path
    pdfFileName = "CombinedExcelSheets.pdf"
    pdfPath = pdfDir & "\" & pdfFileName
    
    'Close any open PDF window
    Application.SendKeys ("%{F4}")
    Application.Wait (Now + TimeValue("0:00:05"))
    
    'Temporary PDF Printer Setup
    Application.SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}{TAB}{RIGHT}{RIGHT}"
    Application.SendKeys "^p{SPACE}" & pdfPath & "{ENTER}"
    Application.Wait (Now + TimeValue("0:00:05"))
    
    'Print each sheet to PDF
    For Each sht In ThisWorkbook.Sheets
        sht.PrintOut Copies:=1, ActivePrinter:="Microsoft Print to PDF"
    Next sht
    
    'Delete the temporary PDF file if it exists
    On Error Resume Next
    Kill pdfPath
    On Error GoTo 0
    
    'Combine all sheets into a single PDF
    Application.SendKeys "^{A}{DEL}" & "{ENTER}"
    Application.SendKeys "^p{SPACE}" & pdfPath & "{ENTER}"
    Application.Wait (Now + TimeValue("0:00:05"))
    
    MsgBox "PDF created successfully at " & pdfPath, vbInformation
End Sub

Let's break down what this script does:

• Set up: It defines variables and sets the path for the output PDF.
• Print Setup: The script closes any open PDF viewer, sets up the temporary PDF printer, and prints each sheet to PDF.
• File Handling: It deletes the temporary PDF file and then combines all sheets into one PDF using Microsoft Print to PDF.
• Notifications: Finally, it notifies the user that the PDF has been created.

Executing the VBA Script

To run the VBA macro:

1. Return to your Excel sheet.
2. Press ALT + F8 to open the Macro dialog.
3. Select CombineSheetsIntoOnePDF and hit Run.

The script will process your Excel workbook, converting all sheets into one PDF file at the specified location.

⚠️ Note: Make sure your sheets contain data and are not protected. Also, save your workbook before running this macro to avoid any data loss.

To recap, we've gone through the process of automating the creation of a PDF document from multiple Excel sheets using VBA. This VBA trick not only enhances efficiency but also provides a consistent format for distributing or archiving data. By combining sheets into one PDF, you ensure that all related information is accessible in one place, making your reports and data sets more manageable and professional.

What happens if the PDF creation fails?

+

The script will notify you with an error message, and you can check the VBA editor’s Immediate Window for more details.

Can this macro work with protected sheets?

+

No, protected sheets need to be unprotected before running the macro as VBA cannot modify or print content on them.

Is there a way to modify the order of sheets in the PDF?

+

Yes, you can manually rearrange the sheets in Excel before running the macro, or you could modify the VBA code to print sheets in a specific order.