VBA: print area to PDF with name & printer selection

[shodan]

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:

   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

 

[John_w]

Try this, changing the folder path and file name as required.

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]

Try this, changing the folder path and file name as required.

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:

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]

Try this - assumes the master sheet is named "Master".

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]

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]

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?

 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]

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