We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

stmoritz
stmoritz asked
on
Medium Priority
16,197 Views
Last Modified: 2012-05-19
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!
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012

Commented:
Yes, it is possible.

Dave
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
Most Valuable Expert 2012
Top Expert 2012

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

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
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
>>>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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

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 ;-)
>>>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
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

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.
In that case did you check the link that I gave above?

Sid

Author

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, ...)
Ok I have Pro 9

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

Sid

Author

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 ;-)
>>>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
stmoritz: I just realized that Dave has already started creating that app for you. Let's wait till he finishes.

Sid
Most Valuable Expert 2012
Top Expert 2012

Commented:
@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
Most Valuable Expert 2012
Top Expert 2012

Commented:
@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
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
It works even with Adobe Acrobat Standard 10, no need for Pro, just fyi.

(of course not just the reader)

Author

Commented:
thank you very much. excellent!
Most Valuable Expert 2012
Top Expert 2012

Commented:
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

Author

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)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.