VBA: print area to PDF with name & printer selection

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
Hi All,

I'm wondering if I can get some help with creating a macro to print a range in excel to PDF. I have started recording some code but this is as far as I can get it:

VBA Code:
   Range("B2:E137").Select
    ActiveSheet.PageSetup.PrintArea = "$B$2:$E$137"
    ActiveSheet.PageSetup.FitToPagesWide = 1
    ActiveSheet.PageSetup.FitToPagesTall = 2
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
    IgnorePrintAreas:=False

I would like to include that it automatically uses the "Microsoft print to PDF" printer and a fixed folder or path the file is printed to .

Any help would be much appreciated
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Try this, changing the folder path and file name as required.
VBA Code:
Sub Macro1()
    Range("B2:E137").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\folder\path\PDF file name.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
 

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
Try this, changing the folder path and file name as required.
VBA Code:
Sub Macro1()
    Range("B2:E137").ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\folder\path\PDF file name.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
Hi John,

Thank you so much for your reply. In the mean time, I have been searching a bit more on the board and found/adjusted some code as such:

VBA Code:
ub PrintSelectionToPDF()
Dim PrivateAttestationRng As Range
Dim strfile As String
Dim dt As String

dt = Format(Now(), "ddmmyyyyhhmmss")
'Setting range to be printed
Set PrivateAttestationRng = Range("B2:E137")
'setting file name with a time stamp.
strfile = Range("D48") & " - " & dt & ".pdf"
'setting the fulli qualified name. The resultent pdf will be saved where the main file exists.
strfile = ThisWorkbook.Path & "/Private Attestation - " & strfile
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 2


PrivateAttestationRng.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strfile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

I do however have one more challenge. I have like about 15 sheets, all identical, apart from the language. I have now inserted a button in each sheet, but Ideally, I would like to be able to print a sheet based on a value of eg cell C17 in my master tab. eg. if C17 = ES, print the sheet called ES.

Is this something you could still help with ?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Try this - assumes the master sheet is named "Master".
VBA Code:
Public Sub Save_Sheet_Range_As_PDF()

    Dim PrivateAttestationRng As Range
    Dim strFile As String
    
    With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets("Master").Range("C17").Value)
        'Range in Master C17-specified sheet to be saved
        Set PrivateAttestationRng = .Range("B2:E137")
        'Full PDF file name with timestamp, in same folder as workbook
        strFile = ThisWorkbook.Path & "\Private Attestation - " & .Range("D48") & " - " & Format(Now, "ddmmyyyyhhmmss") & ".pdf"
        .PageSetup.FitToPagesWide = 1
        .PageSetup.FitToPagesTall = 2
    End With

    PrivateAttestationRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
            
    MsgBox strFile, vbInformation, "Created PDF"

End Sub
 

shodan

Active Member
Joined
Jul 6, 2005
Messages
486

ADVERTISEMENT

Public Sub Save_Sheet_Range_As_PDF() Dim PrivateAttestationRng As Range Dim strFile As String With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets("Master").Range("C17").Value) 'Range in Master C17-specified sheet to be saved Set PrivateAttestationRng = .Range("B2:E137") 'Full PDF file name with timestamp, in same folder as workbook strFile = ThisWorkbook.Path & "\Private Attestation - " & .Range("D48") & " - " & Format(Now, "ddmmyyyyhhmmss") & ".pdf" .PageSetup.FitToPagesWide = 1 .PageSetup.FitToPagesTall = 2 End With PrivateAttestationRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True MsgBox strFile, vbInformation, "Created PDF" End Sub
Hi John,

Just tested and works really fine. Thanks a lot.

I might be pushing my luck now, but I forgot 2 things. I would also always like to print the mastersheet and in some cases, Eg. if the value in C17, is eg. FR; I would like to not only print the FR sheet, but also the NL sheet. I can easily include a small mapping table in a seperate tab, which has the different values from C17, linking with the different language sheets is should print in seperate columns thereafter, or like FR/NL, whatever is easier.

Is this something you could still build in the code ?
 

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
Hi,

a simple change to my sheet allows John's code to work for me. The strange thing is that it does not take over the value of cell G17, for the naming of the file.
in the second repetition of the code. Any idea what is wrong? Is it because it is not within the range?



VBA Code:
 Dim PrivateAttestationRng As Range
    Dim strFile As String
   
    With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets("MASTER_EN").Range("C17").Value)
        'Range in Master C17-specified sheet to be saved
        Set PrivateAttestationRng = .Range("B2:E137")
        'Full PDF file name with timestamp, in same folder as workbook
        strFile = ThisWorkbook.Path & "\Private Attestation - " & .Range("D48") & " - " & .Range("C17") & " - " & Format(Now, "ddmmyyyyhhmmss") & ".pdf"
        .PageSetup.FitToPagesWide = 1
        .PageSetup.FitToPagesTall = 2
    End With
   
   
    PrivateAttestationRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
           
    With ThisWorkbook.Worksheets(ThisWorkbook.Worksheets("MASTER_EN").Range("G17").Value)
        'Range in Master C17-specified sheet to be saved
        Set PrivateAttestationRng = .Range("B2:E137")
        'Full PDF file name with timestamp, in same folder as workbook
        strFile = ThisWorkbook.Path & "\Private Attestation - " & .Range("D48") & " - " & .Range("G17") & " - " & Format(Now, "ddmmyyyyhhmmss") & ".pdf"
        .PageSetup.FitToPagesWide = 1
        .PageSetup.FitToPagesTall = 2
    End With
   
 
  
     PrivateAttestationRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
   
    'MsgBox strFile, vbInformation, "Created PDF"

End Sub
 

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
Hi All,

Thanks for the efforts and big thx to John. I found it. stupid errror in my mapping which should not only had to be done on the master page but on all pages
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top