Add Extra Sheet into a Create PDF Function

  • Hi all.
    Let me preface this my stating that I am a VBA noob (sorry). I can't just start writing VBA and know what I'm doing, but I often find code online for what I'm trying to do and can modify it to suit my needs, but I am having some trouble with something and am hoping someone can help me.


    The end goal is to have a current macro I have which converts a sheet into a PDF and emails it out, to also include an extra sheet (preferably with an easy option to include or not e.g. a cell with a yes/no validation list). The current macro sends out a report built on a pivot table. It changes a pivot table filter to a new variable, creates the PDF and saves it with a specific file name, adds it to an email with subject and body and sends it.


    The Function is as follows;



    and in the macro that seems to call this function I see reference to 'Create_PDF' as follows (apart from the code that sets the pivot table filter, this is also the only section that I can find reference to the sheet "Report");


    Code
    1. FileName = Create_PDF(Sheets("Report"), FilePath & FileName, True, False)


    So "Report" is the main sheet that is converted to PDF and sent, and I am hoping that "Report" and "Extra Page" can be converted in the same PDF. As mentioned above, preferably it could add the extra page if a cell variable was "Yes", otherwise only send "Report".


    Any ideas? I played around in the red underlined section from this text: 'Create_PDF(Sheets("Report"), FilePath & FileName, True, False)', to see if I could add "Extra Page" but nothing I tried worked.


    Many thanks for any assistance.


    Rob

  • Re: Add Extra Sheet into a Create PDF Function


    Hi,


    First of all you have to create a named range for the Extra Page information. I added one more optional boolean parameter 'IncludeExtraPage' within the function so that you can pass the argument as TRUE or FALSE. By default it is FALSE.


    [vb]Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
    OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean, _
    Optional IncludeExtraPage As Boolean = False) As String

    Dim FileFormatstr As String
    Dim Fname As Variant

    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
    & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
    If FixedFilePathName = "" Then
    'Open the GetSaveAsFilename dialog to enter a file name for the pdf
    FileFormatstr = "PDF Files (*.pdf), *.pdf"
    Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
    Title:="Create PDF")
    'If you cancel this dialog Exit the function
    If Fname = False Then Exit Function
    Else
    Fname = FixedFilePathName

    End If
    'If OverwriteIfFileExist = False we test if the PDF
    'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
    If Dir(Fname) <> "" Then Exit Function
    End If
    'Now the file name is correct we Publish to PDF
    On Error Resume Next

    If IncludeExtraPage Then
    '//create a named range 'ExtraPage' . We'll use this name range to include in the report.
    '//Replace the 'ExtraPage' with whatever the name of the range you have created.
    Range("ExtraPage").Copy
    Myvar.Cells(Rows.Count, "a").End(xlUp).Offset(2).PasteSpecial xlPasteValues
    End If

    Myvar.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Fname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=OpenPDFAfterPublish
    On Error GoTo 0
    'If Publish is Ok the function will return the file name
    If Dir(Fname) <> "" Then Create_PDF = Fname
    End If

    End Function[/vb]


    BTW, welcome to board !!!

  • Re: Add Extra Sheet into a Create PDF Function


    Quote from Krishnakumar;732088

    Hi,


    BTW, welcome to board !!!


    Hi Krishnakumar,
    Thanks for your welcome and also for your suggestion to help with my problem. I gave it a go but the result is actually not exactly what I'm looking for. It did add an extra page but it was an extension of the "Report" in that it had the same Print Title/header settings, and only pulled through the text within the named range and not pictures & other formatting.


    "Report" & "Extra Page" will have their own print settings so I need them to be treated independently, but combined in the one PDF.


    Basically the desired output is the same as if I was to group the "Report" & "Extra Page" sheets, and then use the "File > Save & Send > Create PDF/XPS Document" option.


    Any other ideas?


    Rob.

  • Re: Add Extra Sheet into a Create PDF Function


    OK.


    Note: adjust the sheet name (extra page) in the code


    [vb]Function Create_PDF(Myvar As Object, FixedFilePathName As String, _
    OverwriteIfFileExist As Boolean, OpenPDFAfterPublish As Boolean, _
    Optional IncludeExtraPage As Boolean = False) As String

    Dim FileFormatstr As String
    Dim Fname As Variant

    Const ExtraPageSheetName As String = "Extra Page" '<<<<<<<<< adjust the sheet name

    'Test If the Microsoft Add-in is installed
    If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
    & Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") <> "" Then
    If FixedFilePathName = "" Then
    'Open the GetSaveAsFilename dialog to enter a file name for the pdf
    FileFormatstr = "PDF Files (*.pdf), *.pdf"
    Fname = Application.GetSaveAsFilename("", filefilter:=FileFormatstr, _
    Title:="Create PDF")
    'If you cancel this dialog Exit the function
    If Fname = False Then Exit Function
    Else
    Fname = FixedFilePathName

    End If
    'If OverwriteIfFileExist = False we test if the PDF
    'already exist in the folder and Exit the function if that is True
    If OverwriteIfFileExist = False Then
    If Dir(Fname) <> "" Then Exit Function
    End If
    'Now the file name is correct we Publish to PDF
    On Error Resume Next

    If IncludeExtraPage Then
    Worksheets(Array(Myvar.Name, ExtraPageSheetName)).Copy
    ActiveWorkbook.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Fname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=OpenPDFAfterPublish
    ActiveWorkbook.Close 0
    Else
    Myvar.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Fname, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=OpenPDFAfterPublish
    End If

    On Error GoTo 0
    'If Publish is Ok the function will return the file name
    If Dir(Fname) <> "" Then Create_PDF = Fname
    End If

    End Function[/vb]