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

Andrew_W

New Member
Joined
Dec 7, 2017
Messages
3
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
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.
 
Upvote 0
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
 
Upvote 0
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:
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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
Back
Top