In Excel VBA, how do you export as PDF and embed the original Excel File?
Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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

  2. #2
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

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

    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("BookName").Select
    
    Range("A1:End Address").Select
       Selection.Copy
    
        ChDir "C:\DirDestination"
        Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\DirDestination\BookName" & ".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.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  3. #3
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    4,949
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Andrew_W View Post
    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

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Thank you so much John! This looks great!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com