Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-27
24
Medium Priority
?
11,567 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!
0
Comment
Question by:stmoritz
  • 9
  • 7
  • 7
  • +1
24 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 35477635
Yes, it is possible.

Dave
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35477702
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
 
LVL 42

Expert Comment

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

Dave
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 42

Expert Comment

by:dlmille
ID: 35477741
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35477750
>>>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
 
LVL 42

Expert Comment

by:dlmille
ID: 35477775
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
 

Author Comment

by:stmoritz
ID: 35478009
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35478022
>>>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
 
LVL 42

Expert Comment

by:dlmille
ID: 35478028
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
 

Author Comment

by:stmoritz
ID: 35478059
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35478162
In that case did you check the link that I gave above?

Sid
0
 

Author Comment

by:stmoritz
ID: 35478437
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35478594
Ok I have Pro 9

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

Sid
0
 

Author Comment

by:stmoritz
ID: 35478637
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35478711
>>>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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35479022
stmoritz: I just realized that Dave has already started creating that app for you. Let's wait till he finishes.

Sid
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35479827
@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
 
LVL 42

Expert Comment

by:dlmille
ID: 35479932
@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
 
LVL 42

Accepted Solution

by:
dlmille earned 2000 total points
ID: 35480300
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
 

Author Comment

by:stmoritz
ID: 35484843
It works even with Adobe Acrobat Standard 10, no need for Pro, just fyi.

(of course not just the reader)
0
 

Author Closing Comment

by:stmoritz
ID: 35484848
thank you very much. excellent!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35484883
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
 

Author Comment

by:stmoritz
ID: 35484991
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
 

Expert Comment

by:Rayne
ID: 37988867
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question