Print TO PDF in VBA

[PassRusher]

So, for years, we've had a VBA "button" to either print the current page or print the entire workbook. I want to take some of this code and turn into print page as PDF or print workbook as PDF...here is the existing code...

Print Page

Sub PRINT_PAGE()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$H$" & Simple_LastRowOnPage
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Print Workbook (Shipper, as we call it)

Sub PRINT_SHIPPER()
    Application.ScreenUpdating = False

    Dim strSheetName As String
    Dim strLastRow As String
    Dim decLastRow As Variant
    Dim blnCees As Boolean
    Dim d As Double
    Dim ws As Worksheet

    Sheets("COVER").Visible = False
    Application.Calculation = xlCalculationManual
    For Each ws In Worksheets
        If ws.Visible = True Then
            ws.Select
            With ActiveSheet.PageSetup
                .PrintTitleRows = "$1:$12"
                .PrintErrors = xlPrintErrorsDisplayed
            End With
            Select Case UCase(Left(ws.Name, 4)) ' Or UCase(Right(ws.Name, 4))
                Case "TRIM"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$J$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                Case "PURL"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$J$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                Case "MEMB"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                Case "PANE"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                    
               Case "ACCE"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                    
               Case "SPCL"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                    
                Case "PINN"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                
                Case "SSR "
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
               Case "CEE "
                    With ActiveSheet.PageSetup
                        .PrintArea = "$A$1:$J$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col J to range BW
                    End With
                Case "HARD"
                    With ActiveSheet.PageSetup
                        .PrintArea = "$A$1:$H$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col H to range BW
                    End With
                Case "INSU"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$H$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col H to range BW
                    End With
                Case "BUYO"
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col H to range BW
                    End With
                Case Else
                    With ActiveSheet.PageSetup
                         Columns("H").Hidden = True ' -- Hides Wt/Pc so that REV will print 08/13/2020 BW
                        .PrintArea = "$A$1:$I$" & Simple_LastRowOnPage '-- 02/11/10 MAL -- 8/13/2020 Added Col H to range BW
                    End With
                Columns("H").Hidden = False ' -- Unhides Wt/Pc so that REV will print 08/13/2020 BW
            End Select
        End If
    Next ws
    blnCees = Sheets("CEE ORDER").Visible
    Sheets("CEE ORDER").Visible = False
    ActiveWorkbook.PrintOut Copies:=1, Collate:=True
    Application.Calculation = xlCalculationAutomatic
    Sheets("CEE ORDER").Visible = blnCees
    Sheets("COVER").Visible = True
    Sheets("COVER").Select
    Application.ScreenUpdating = True
    
End Sub

So looking for some help on how to get it to print to PDF. Sure, they can go to file, print, print to PDF but this does make it easier for them and yeah, management is requesting it. They all use Bluebeam so was wondering if we can use that or somehow access the built in Microsoft Print to PDF option for this.  

[#2]

@PassRusher 

To modify your VBA code to print to PDF in Excel, you can use the "ExportAsFixedFormat" method. Here's how you can adapt your code for printing to a PDF file:

For printing the active sheet as a PDF:

vba code:

Sub PRINT_PAGE_PDF()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim PDFPath As String
    PDFPath = "C:\Path\To\Your\File.pdf" ' Set the desired PDF file path

    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$H$" & Simple_LastRowOnPage
    End With

    ' Export the active sheet as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Make sure to change C:\Path\To\Your\File.pdf to the desired path where you want to save the PDF.

For printing the entire workbook as a PDF:

vba code:

Sub PRINT_WORKBOOK_PDF()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim PDFPath As String
    PDFPath = "C:\Path\To\Your\File.pdf" ' Set the desired PDF file path

    ' Set the entire workbook as the print area
    ActiveWorkbook.Sheets.PrintOut

    ' Export the entire workbook as PDF
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

This code will export the active sheet or the entire workbook as a PDF file. You can change the PDFPath variable to specify the location and name of the PDF file you want to create.

Remember to replace "C:\Path\To\Your\File.pdf" with the desired path and filename for your PDF. You can also use a file dialog to let users choose the location and name for the PDF if needed.

Note that the file path should be a valid directory path on your computer, and the filename should end with ".pdf" for it to be saved as a PDF file. The text, _Steps and Code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

Sub PrintActiveSheetToPDF()
Dim filePath As String
filePath = "C:\path\to\your\file.pdf" ' Change this to your desired file path and name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

[#4]

This code will save the currently active sheet as a PDF file :

Sub PrintWorkbookToPDF()
Dim filePath As String
filePath = "C:\path\to\your\file.pdf" ' Change this to your desired file path and name
ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub