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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

BrianJN1

Well-known Member
Joined
Jun 15, 2017
Messages
603
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.
 

Andrew_W

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,611
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,496
Messages
5,596,502
Members
414,073
Latest member
Contilly

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