Create PDF from Excel VBA

To create a PDF from an Excel worksheet using VBA, follow these detailed steps:

Step 1: Enable Developer Tab

Before you can write and run VBA code, you need to enable the Developer tab in Excel. Here’s how:

1. Open Excel.
2. Click on the File tab.
3. Select Options.
4. In the Excel Options window, choose Customize Ribbon on the left.
5. On the right, check the box next to Developer under the Main Tabs section.
6. Click OK to enable the Developer tab.

Step 2: Open Visual Basic for Applications (VBA) Editor

To open the VBA Editor:

1. Go to the Developer tab in Excel.
2. Click on Visual Basic to open the Visual Basic for Applications (VBA) editor, where you will write your code.

Step 3: Insert a New Module

To insert a new module:

1. In the VBA editor, click on Insert in the menu.
2. Select Module. This will create a new module in the editor where you can write your VBA code.

Step 4: Write VBA Code

Now, write the VBA code that will create a PDF from an Excel sheet. Here’s an example code:

Sub CreatePDF()
    Dim ws As Worksheet
    Dim pdfFileName As String   
    ' Reference the worksheet you want to save as PDF (Here, it's the active sheet)
    Set ws = ThisWorkbook.ActiveSheet   
    ' Define the path and name for the PDF file
    pdfFileName = "C:\Users\YourUsername\Documents\ExcelToPDF.pdf"   
    ' Export the worksheet to PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard   
    ' Notify user that the PDF has been created
    MsgBox "PDF created successfully at: " & pdfFileName, vbInformation
End Sub

Explanation of the Code:

• Dim ws As Worksheet: This line defines a variable ws to store a reference to the worksheet you want to export to PDF.
• Dim pdfFileName As String: This line defines a variable pdfFileName to store the location and name of the PDF file.
• Set ws = ThisWorkbook.ActiveSheet: This line references the currently active worksheet in the workbook (i.e., the sheet you’re working with).
• pdfFileName = « C:\Users\YourUsername\Documents\ExcelToPDF.pdf »: This line defines the path where the PDF will be saved. You should replace YourUsername with your actual username or desired directory path.
• ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfFileName, Quality:=xlQualityStandard: This is the core command that exports the worksheet as a PDF. The Type is set to xlTypePDF, and the Filename is set to the pdfFileName you defined earlier. xlQualityStandard ensures that the PDF is saved in a standard quality format.
• MsgBox « PDF created successfully… »: This line shows a message box to the user, confirming that the PDF was created successfully and providing the path to the file.

Step 5: Run the Macro

To run the macro:

1. Press F5 in the VBA editor or go back to Excel and run the macro from the Developer tab by clicking Macros.
2. Select the CreatePDF macro and click Run.

Step 6: Check Output

Once the macro runs, check the file path you specified in the code (C:\Users\YourUsername\Documents\ExcelToPDF.pdf). You should find the PDF file created with the contents of the active Excel sheet.

Additional Tips:

• Customizing Output: You can customize the PDF output by adjusting the ExportAsFixedFormat parameters. For example, you can specify the range of cells to export, include page breaks, or set the quality.
• Error Handling: For more robust code, you might want to add error handling to ensure that the file path is valid and that any issues are caught and reported to the user.

This process allows you to easily convert any Excel worksheet to a PDF using VBA.