Forum Discussion

Copper Contributor

Oct 20, 2023

Print TO PDF in VBA

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.

excel

Macros and VBA

4 Replies

NikolinoDE
Gold Contributor
Oct 21, 2023
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.
- CYBERROGER
  Copper Contributor
  Aug 13, 2024
  NikolinoDE
  
  Can I variabilise the path and file name using &range(C1) and put in C1 the path and file name
  Better can I set up 2 variables:
  one in C1 for the path
  and one in D1 for the file name
  it would read &RANGE(C1) &RANGE(D1)
  
  OR SOMETHING SIMILAR IF YOU KNOW BETTER
- suraj786
  Copper Contributor
  Oct 27, 2023
  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
- suraj786
  Copper Contributor
  Oct 27, 2023
  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

Resources

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

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

Sub PRINT_PAGE_PDF()\n Application.ScreenUpdating = False\n Application.Calculation = xlCalculationManual\n Dim PDFPath As String\n PDFPath = \"C:\\Path\\To\\Your\\File.pdf\" ' Set the desired PDF file path\n\n With ActiveSheet.PageSetup\n .PrintArea = \"$A$1:$H$\" & Simple_LastRowOnPage\n End With\n\n ' Export the active sheet as PDF\n ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False\n\n Application.Calculation = xlCalculationAutomatic\n Application.ScreenUpdating = True\nEnd Sub

Sub PRINT_WORKBOOK_PDF()\n Application.ScreenUpdating = False\n Application.Calculation = xlCalculationManual\n Dim PDFPath As String\n PDFPath = \"C:\\Path\\To\\Your\\File.pdf\" ' Set the desired PDF file path\n\n ' Set the entire workbook as the print area\n ActiveWorkbook.Sheets.PrintOut\n\n ' Export the entire workbook as PDF\n ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFPath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False\n\n Application.Calculation = xlCalculationAutomatic\n Application.ScreenUpdating = True\nEnd Sub