Forum Discussion
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.
4 Replies
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 SubMake 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 SubThis 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.
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
- 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 - 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
