In Excel VBA, how do you export as PDF and embed the original Excel File?

[Andrew_W]

Hi,

I am trying to export an Excel file to a PDF and include the original Excel file as an attachment in the PDF using VBA.

I have looked in a number of places but have not been successful in finding complete information - particularly for attaching a file to the PDF as an attachment.

Any help is greatly appreciated.

Andrew

 

[BrianJN1]

This works for me as I need to import a mere section of a worksheet, not the complete sheet not the whole workbook.

I'll provide my thoughts anyway in the event that you may derive something from it, but also, by bumping it, it may draw attention who might further assist both of us.

My code:

Sheets("[B][I]BookName[/I][/B]").Select

Range("A1:[B][I]End Address[/I][/B]").Select
   Selection.Copy

    ChDir "C:\DirDestination"
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\DirDestination\[I][B]BookName[/B][/I]" & ".pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False

I have tried to modify the macro prior to posting to select one complete worksheet but that seems to appear in the PDF as blank. In doing that I ignored selecting a range.

 

[Andrew_W]

Thank you very much Brian,

The code you provide creates a PDF from Excel - which is the first half of the problem.

The second half - and the part I am having great difficulty with - is then having another file included in the newly created PDF as an attachment. (In my specific case, I want to include the Excel file that created the PDF to begin with).

Any thoughts on that one?

Thanks,

Andrew

 

[John_w]

The second half - and the part I am having great difficulty with - is then having another file included in the newly created PDF as an attachment. (In my specific case, I want to include the Excel file that created the PDF to begin with).

This can be done with VBA, but only if you have Acrobat Professional installed on your computer. Acrobat Pro has the library objects which can be called from VBA.

Example code:

'Requires reference to Adobe Acrobat x.0 Type Library

Public Sub Attach_Workbook_to_PDF()

    Dim AcroPDDoc As Acrobat.CAcroPDDoc
    Dim JSO As Object
    Dim inputPDFfile As String, outputPDFfile As String
    Dim workbookFile As String
    
    'The workbook to be attached to the PDF file
    
    workbookFile = "C:\folder\Workbook.xlsx"
    
    'The PDF file to contain the workbook
    
    inputPDFfile = "C:\folder\PDF file.pdf"

    'File name which the PDF file will be saved as - can be same as the input PDF file or a different name
    
    outputPDFfile = inputPDFfile
    
    Set AcroPDDoc = CreateObject("AcroExch.PDDoc")
    
    If AcroPDDoc.Open(inputPDFfile) Then

        Set JSO = AcroPDDoc.GetJSObject
        
        'Attach the Excel workbook to the PDF file
        
        If JSO.importDataObject(Mid(workbookFile, InStrRev(workbookFile, "\") + 1), workbookFile) Then
        
            'Save and close the output PDF file
            
            AcroPDDoc.Save 1, outputPDFfile
            AcroPDDoc.Close
            MsgBox "Created " & outputPDFfile & vbNewLine & "containing " & workbookFile
        
        Else
            
            MsgBox "Unable to attach " & workbookFile & vbNewLine & " to " & inputPDFfile
        
        End If
        
    Else
    
        MsgBox "Unable to open " & vbNewLine & inputPDFfile
    
    End If

End Sub

 

[Andrew_W]

Thank you so much John! This looks great!