Is it possible with Excel (2010) VBA to append page (print range from sheet) to existing pdf?

I was wondering whether there is a possibility to have an Excel 2010 VBA code append a page to an existing pdf? So create a pdf from the print range of the active sheet and append it as last page to the pdf file (path/name stored in cell A1 of active sheet)?

50 points for yes/no whether possible
450 points for possible solution (i would then increase points afterwards)

Thanks a lot!
stmoritzAsked:
Who is Participating?
 
dlmilleCommented:
Crediting: Source:  http://www.rondebruin.nl/pdf.htm - for the RDB_Create_PDF function (I put all Ron's functions in the attached) which handles printing the sheet to a PDF File
Crediting: Source: http://vbcity.com/forums/t/143916.aspx - for the starting code, which had to stand some modification, that does the merge function - and Sid for tee-ing this one up!
----------------------------------------------------------

Ok.  Here's the solution, with a few "adds" that are easily reversable by you or I, depending on need.  Be sure to add the reference to the Acrobat library in the Tools->Reference area.  This solution indeed requires the PRO version of Acrobat.

----------------------------------------------------------

First, the "existing" PDF is in Range A1 of the Active Worksheet.  I also added the "Appended" PDF in  Range A2 of the Active Worksheet.  I felt it would be good to manage failures that way, rather than lose a valuable source PDF :)

Second, I adapted the above merge code to work properly with late binding (as posted) and a few additional parameters:

Private Sub MergePDF(sourceAppend As String, destStart As String, finishOut As String, bSilent As Boolean)sourceAppend - is the file to add to the end
destStart - is the file to start with
finishOut - is the resultant merged file
bSilent - TRUE - runs in silent mode, FALSE - message prompting with option to see the merged file

 
Private Sub MergePDF(sourceAppend As String, destStart As String, finishOut As String, bSilent As Boolean)
'Relies on the Adobe Acrobat X.X Type Library - just add Tools->References->Acrobat and it will work, accordingly
Dim objAcroApp As Object
Dim objCAcroPDDocDest As Object
Dim objCAcroPDDocSource As Object
Dim avDOC As Object
Dim xMsg As Integer
Dim bSuccess As Boolean

    'Initialize the objects
    Set objAcroApp = CreateObject("AcroExch.App")
    Set objCAcroPDDocDest = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")

    If objCAcroPDDocDest.Open(destStart) Then

          objCAcroPDDocSource.Open (sourceAppend)
          If objCAcroPDDocDest.InsertPages(objCAcroPDDocDest.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            bSuccess = True
          Else
            '0 problem
          End If
          objCAcroPDDocSource.Close
        
        objCAcroPDDocDest.Save 1, finishOut
        
    End If

    If bSuccess And Not bSilent Then

        xMsg = MsgBox("Documents Merged!  Open and Review?", vbYesNo, "Hit Yes to open PDF for review")
        
        If xMsg = vbYes Then
            
            objAcroApp.Show
            
            Set avDOC = objCAcroPDDocDest.OpenAVDoc("")
            
            MsgBox "Hit Ok to close it out and continue...", vbOKOnly, "Hit Ok, Ok? :)"
            
            objAcroApp.Hide
            
            avDOC.Close (True)

            Set avDOC = Nothing
        End If
        
    End If
    
        objCAcroPDDocSource.Close
        objCAcroPDDocDest.Close
        
        Set objCAcroPDDocSource = Nothing
        Set objCAcroPDDocDest = Nothing
        
        objAcroApp.Exit
        
        Set objAcroApp = Nothing

End Sub

Open in new window



Then, I created this routine, which prints to a temporary PDF, then calls the merge routine for appending:
Sub printAppend_PDF(Sh As Worksheet, existingPDF As String, mergedPDF As String, bSilent As Boolean)Sh - worksheet for printing the current print range
existingPDF - the original to be appended to
mergedPDF - the resultant output file
bSilent - TRUE - runs in silent mode, FALSE - message prompting with option to see the merged file

 
Sub printAppend_PDF(Sh As Worksheet, existingPDF As String, mergedPDF As String, bSilent As Boolean)
Dim fTempPDF As String

    fTempPDF = RDB_Create_PDF(Sh, Application.DefaultFilePath & "\temp.pdf", True, False) 'True - overwrite if exists, True - open PDF when complete

    If fTempPDF <> "" Then
        Call MergePDF(fTempPDF, existingPDF, mergedPDF, bSilent) 'bSilent=TRUE - Message Prompt with option to open PDF after, FALSE - Silent mode
        Kill fTempPDF 'then delete fTempPDF
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If
End Sub

Open in new window



Finally, the DRIVER routine - modify this as you wish to append in different approaches.  This one addresses the Solution directly, grabbing the active sheet references to the existing file, and to-be merged file.
 
Sub testPrintAppend()
Dim existingPDF As String
Dim mergedPDF As String

    existingPDF = ActiveSheet.Range("A1").Value
    mergedPDF = ActiveSheet.Range("A2").Value
    
    Call printAppend_PDF(ActiveSheet, existingPDF, mergedPDF, False) 'False - Message Prompting and Option to Open, True - Silent Mode
    
End Sub

Open in new window



A final note - you can make the merged file the same as the original file and it should work just fine - so you can change the DRIVER routine and thus only have Range A1 if you so choose.

Please give it a try and advise if it works well for you!

Enjoy!

Dave


PDF-Append-r2.xlsm
0
 
dlmilleCommented:
Yes, it is possible.

Dave
0
 
SiddharthRoutCommented:
I think it is possible. I have never personally tried it. See this link. :)

Topic: Merge multi PDF files from VBA
Link: http://vbcity.com/forums/t/143916.aspx

Sid
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dlmilleCommented:
I'm working on the solution, now.  Do you have Acrobat Pro, or other software, or just Acrobat reader installed?

Dave
0
 
dlmilleCommented:
PS - I had found the same source, and was looking at that and:

http://msdn.microsoft.com/en-us/library/ee834871(office.11).aspx

http://www.exp-systems.com/forum_exp/forum_posts.asp?TID=194

Solution forcoming.

Dave
0
 
SiddharthRoutCommented:
>>>Acrobat reader installed?

Dave I believe and I could be wrong but you cannot merge documents using just Acrobat reader. You will have to have a PRO.

Sid
0
 
dlmilleCommented:
Actually there are various options I'm working.  I just wanted to know what the Asker has installed, so I can tailor my response.

Response forthcoming, unless anyone has more questions, lol :)

Dave
0
 
stmoritzAuthor Commented:
On my machine Adobe Acrobat 9 Pro, but most machines have only Adobe Acrobat 9 or 10 Standard.

Of course, I'd prefer an Office only solution in order we can finally get rid off Adobe ;-)
0
 
SiddharthRoutCommented:
>>>Of course, I'd prefer an Office only solution in order we can finally get rid off Adobe ;-)

Oops. No, I don't think there is 'Office Only' solution. You will have to take the help of a pdf writer from VBA :)

Sid
0
 
dlmilleCommented:
That's why I asked, and I'm searching for a PRO-independent way.  However, in the meantime and 90% likely we'll have to go this way, PRO may be required.  There are, however, "freeware" PRO-like solutions.

Let me finish my PRO-dependent version, and we can discuss if there is an alternative that's non-PRO or options for the "freeware" approach.

Ok?

Dave
0
 
stmoritzAuthor Commented:
as long as I can click one button in xls and start the code, I am happy, whether Adobe or not :o))
very happy, indeed.
0
 
SiddharthRoutCommented:
In that case did you check the link that I gave above?

Sid
0
 
stmoritzAuthor Commented:
Hi Sid. I tried it, but it seems it is an old reference library.

'Relies on the Adobe Acrobat 6.0 Type Library

I have tons of acrobat ones, but not one worked with this code... (Distiller, PDFmaker, ...)
0
 
SiddharthRoutCommented:
Ok I have Pro 9

Let me create a sample for you. I actually got stuck in a very important discussion :)

Sid
0
 
stmoritzAuthor Commented:
you have Pro 9 and it works? what library did you use then?

sorry I don't want to interrupt or steal time from that very important discussion ;-)
0
 
SiddharthRoutCommented:
>>>you have Pro 9 and it works? what library did you use then?

No I haven't yet tried it. I am creating an application from scratch.

>>>Sorry I don't want to interrupt or steal time from that very important discussion ;-)

No it's not important. Was just joking. We were just having some fun in the Exhale Fun Thread

It sometime gets too monotonous here :)

Sid
0
 
SiddharthRoutCommented:
stmoritz: I just realized that Dave has already started creating that app for you. Let's wait till he finishes.

Sid
0
 
dlmilleCommented:
@stmoritz - sorry its been a while - had to eat and take care of some work issues, lol....

I've finally got my side working correctly - was bombing on the code, which I modified and should work for both of us...

This is just a test for you to try out while I write the algorithm now to do what you want.  THIS POST IS NOT THE SOLUTION, JUST A TEST..
Private Sub MergePDF(sourceAppend As String, destStart As String, finishOut As String)
'Relies on the Adobe Acrobat X.X Type Library - just add Tools->References->Acrobat and it will work, accordingly
Dim objAcroApp As Object
Dim objCAcroPDDocDest As Object
Dim objCAcroPDDocSource As Object

    'Initialize the objects
    Set objAcroApp = CreateObject("AcroExch.App")

    Set objCAcroPDDocDest = CreateObject("AcroExch.PDDoc")
    Set objCAcroPDDocSource = CreateObject("AcroExch.PDDoc")
    
    'Open Destination, all other documents will be added to this and saved with a new filename
    
    If objCAcroPDDocDest.Open(destStart) Then
        
        'Do your loop here to open subsequent documents that you want to add
        'Do
          'Open the source document that will be added to the destination
          objCAcroPDDocSource.Open (sourceAppend)
          If objCAcroPDDocDest.InsertPages(objCAcroPDDocDest.GetNumPages - 1, objCAcroPDDocSource, 0, objCAcroPDDocSource.GetNumPages, 0) Then
            MsgBox "Documents Merged!"
          Else
            '0 problem
          End If
          objCAcroPDDocSource.Close
        'loop
        
        objCAcroPDDocDest.Save 1, finishOut
        objCAcroPDDocDest.Close
        
    End If
    Set objCAcroPDDocSource = Nothing
    Set objCAcroPDDocDest = Nothing

End Sub
Sub testMerge()

    Call MergePDF("C:\Users\DMBW\Desktop\test.pdf", "c:\users\dmbw\desktop\testprint.pdf", "C:\users\dmbw\desktop\merged.pdf")
    
End Sub

Open in new window

So, if you've added Acrobat to your library reference, then take this code and modify the paths to the testMerge() code I wrote, test, and advise it works in your environment.

Real solution now in progress!

Dave
0
 
dlmilleCommented:
@stmoriz

PS - to add Acrobat reference library, just click on "Acrobat" in the Tools References, then the library will be added, assuming you have pro
0
 
stmoritzAuthor Commented:
It works even with Adobe Acrobat Standard 10, no need for Pro, just fyi.

(of course not just the reader)
0
 
stmoritzAuthor Commented:
thank you very much. excellent!
0
 
dlmilleCommented:
Thanks for the clarification.  Without more depth on the Acrobat script, I only had to go with the setup I had, rather than partitions.  Great to hear it works with Standard editions.  I need to explore the objects more and this was a great primer for me.

Dave
0
 
stmoritzAuthor Commented:
I need to explore the objects more and this was a great primer for me.
glad, that you were able to benefit as well!  :o)
0
 
RayneCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.