4 Mar 2009

Adobe Acrobat and VBA – An Introduction

Posted by khk

Update:

Please visit the same post on my business site. The comments are closed here, so if you want to comment, you have to head over to http://khkonsulting.com/2009/03/adobe-acrobat-and-vba-an-introduction/

Here is another topic that comes up every now and then: How can I “talk” to Adobe Acrobat from e.g. MS Excel via VBA? I’ll try to give an introduction into that subject in this document. I will only discuss the basics, but I’m open for suggestions about what part to discuss next. So keep the comments coming.

More after the jump…

The Warning Upfront

Before we get too deep into this, let me say this: I am not a VBA expert. I do not program in VBA or VB. All I know about VB is from googling a few things and looking at sample code. It does help that I’ve programmed in many (make that a capital ‘M’ Many) programming languages, and at the end most of them share enough characteristics that once you know one, you know all of them… But still, don’t consider my VB programs to be at an expert level. I only use the samples to demonstrate general methods. It’s up to you to fill in all the missing details (e.g. exception handling).

Resources

All this information is available in one form or another in Adobe’s SDK documentation. Before you read any further, click on this link and take a look at what they have available.

There are (at least) two documents that are required reading if you want to use Acrobat from within your VBA code:

 

 

 

If you want to utilize the VB/JavaScript bridge, you also should read the JavaScript related documents:

 

 

 

All of these documents can also be accessed via Adobe’s online documentation system. In order to find the documents I’ve listed above, you need to expand the tree on the left side of the window for the “JavaScript” and “Acrobat Interapplication Communication” nodes.

There is always more than one way…

There are two ways your program can interact with Acrobat. One is more direct than the other, but both require the same mechanism to get things started…
You can either use the “normal” IAC (Inter Application Communication) interface, which is basically a COM object that your program loads and uses to communicate with Acrobat, or you can use the VB/JavaScript bridge, which allows access to Acrobat’s JavaScript DOM. The latter case still requires that your program first establishes a connection to Acrobat via IAC.

Let’s get the party started

As I mentioned before, regardless of how we want to remote control Adobe Acrobat from VB, we need to establish a connection to it’s COM object (or OLE server). You may have noticed that I always talk about “Adobe Acrobat”, and not the “Adobe Reader”. What I’m presenting here is valid for the Adobe Acrobat, Reader only supports a small subset of features. To learn more about what the differences are, see the IAC Developer Guide. For the purpose of this document, I will use MS Excel 2007 and Adobe Acrobat 9 Pro. As long as you have a version of Acrobat that is compatible with the version of VBA that you are using, you should be able to follow along without any problems.

Preparing MS Excel 2007

When you install Office 2007 or Excel 2007, make sure that you select the Visual Basic Editor component, otherwise you will not be able to write VBA code. This is different than all the versions up to 2007. Once installed, you need to add the “Developer” tab to the ribbon. This is done on the Excel Options dialog, under the Popular category:

excel_options.png

Once that is done, you should see the “Developer” tab as part of the ribbon:

developer_tab.png

Our First Button

Open a new document and select the Developer tab. Then go to the Insert control and place a button on your document. This will pop up the “Assign Macro” dialog, just click on the “Add” button, which will bring up the VBA editor. Nothing special so far.

Before we can use any of Acrobat’s functionality, we need to make sure that VBA knows about the Acrobat objects. On the VBA dialog, select the “Tools>References” menu item. On the dialog that pops up, make sure that the TLB for your version of Acrobat is selected. This is what it looks like for my system:

references.png

Now we can add code that references the Acrobat objects to our button handler. Of course, before we do that, we need to decide what our button is actually supposed to trigger. Let’s start with something simple – let’s combine two PDF documents and save the result as a new document.

I’ll present the whole program first, and will then explain the different parts.

Sub Button1_Click()

    Dim AcroApp As Acrobat.CAcroApp

    Dim Part1Document As Acrobat.CAcroPDDoc
    Dim Part2Document As Acrobat.CAcroPDDoc

    Dim numPages As Integer

    Set AcroApp = CreateObject("AcroExch.App")

    Set Part1Document = CreateObject("AcroExch.PDDoc")
    Set Part2Document = CreateObject("AcroExch.PDDoc")

    Part1Document.Open ("C:\temp\Part1.pdf")
    Part2Document.Open ("C:\temp\Part2.pdf")

    ' Insert the pages of Part2 after the end of Part1
    numPages = Part1Document.GetNumPages()

    If Part1Document.InsertPages(numPages - 1, Part2Document, 
    0, Part2Document.GetNumPages(), True) = False Then
        MsgBox "Cannot insert pages"
    End If

    If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False Then
        MsgBox "Cannot save the modified document"
    End If

    Part1Document.Close
    Part2Document.Close

    AcroApp.Exit
    Set AcroApp = Nothing
    Set Part1Document = Nothing
    Set Part2Document = Nothing

    MsgBox "Done"

End Sub

Save the document. When prompted for a filename and a filetype, select the type of “Excel Macro-Enabled Workbook” – otherwise the program you just added will get stripped out of the file.

Make sure that there are two files named Part1.pdf and Part2.pdf in the c:\temp directory.

Click the button and enjoy…

After the program is done, there will be a new file C:\Temp\MergedFile.pdf on your disk. Open that in Acrobat, and verify that it indeed contains the results of concatenating the two source files.

So, how does it work?

The whole program is in a button handler.

Sub Button1_Click()
...
End Sub

Let’s now look at the different parts of that handler.

At first, we need to setup a bunch of objects that we will use further down the code:

    Dim AcroApp As Acrobat.CAcroApp
    Dim Part1Document As Acrobat.CAcroPDDoc
    Dim Part2Document As Acrobat.CAcroPDDoc
    Dim numPages As Integer

The first statement sets up an object of type Acrobat.CAcroApp – this reflects the whole Acrobat application. If you look through the documentation, you’ll see that there are a number of things that can be done on the application level (e.g. minimizing or maximizing the window, executing menu items, retrieve preference settings, closing the application, …). The next two lines declare two objects of type Acrobat.CAcroPDDoc – these reflect the two documents that we need to open.

There are two different document types available in the OLE part of IAC: The AVDoc and the PDDoc. An AVDoc is one that gets opened in Acrobat’s user interface, the user can navigate through it’s pages, and do anything that you can do with a PDF document when you double-click on it to open it in Acrobat. A PDDoc on the other hand gets opened in the background. Acrobat still has access to it, and can manipulate it, but the user does not see it. This is useful if a program should quietly do it’s work without showing the user what’s going on.

Every AVDoc has a PDDoc behind the scenes, and that object can be retrieved via the AVDoc.GetPDDoc method. A PDDoc only has an associated AVDoc if it is actually shown in Acrobat, however, we cannot retrieve that AVDoc object from within the PDDoc. This sounds complicated, but once you get more familiar with how these things are used, it becomes second nature.

We also need an integer object to store the number of pages in the first document.

    Set AcroApp = CreateObject("AcroExch.App")
    Set Part1Document = CreateObject("AcroExch.PDDoc")
    Set Part2Document = CreateObject("AcroExch.PDDoc")

In the next step, we initialize the three Acrobat related objects. Nothing special here.
    Part1Document.Open ("C:\temp\Part1.pdf")
    Part2Document.Open ("C:\temp\Part2.pdf")

Now that our objects are initialized, we can use the methods to do something with the objects. In order to merge files, we need access to both the source files, so we have to call the Open() method on both these objects. The key to success is to specify the whole path name, directory and filename.
    numPages = Part1Document.GetNumPages()

The method InsertPages requires that we specify after which page to insert the second document. Because we want to insert the pages after the last page of the first document, we need to find out how many pages we have in that document. The GetNumPages() method does return that information.

This is also, where it becomes a bit tricky: Acrobat starts to count the pages in a PDF document at zero. So, if we want to insert the pages after the first page in the document, we need to insert after page number zero. If we want to insert after the second page, we need to insert after page number one… Because we want to insert the pages after the last page of the first document, we need to insert the pages after (lastPage-1). Again, this is a bit confusing, but after a while it gets easier.

    If Part1Document.InsertPages(numPages - 1, Part2Document, 
    0, Part2Document.GetNumPages(), True) = False Then
        MsgBox "Cannot insert pages"
    End If

This is where Acrobat does all it’s work. The parameters of the InsertPages method are described in the Interapplication Communication API Reference document: InsertPages

Now we only have to save the document, do some cleanup and exit our program:

    If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False Then
        MsgBox "Cannot save the modified document"
    End If

    Part1Document.Close
    Part2Document.Close

    AcroApp.Exit
    Set AcroApp = Nothing
    Set Part1Document = Nothing
    Set Part2Document = Nothing

    MsgBox "Done"

With these steps, and the information in the API documentation, you should be able to write simple programs.

I’ll document the VB/JavaScript bridge in my next posting.

Tags: , , , , ,

Subscribe to Comments

111 Responses to “Adobe Acrobat and VBA – An Introduction”

  1. […] I’ve described in one of my previous posting, it is quite easy to automate Acrobat from VB or VBA. So how does JavaScript fit into this picture? […]

     
  2. Hi, I am using Vista x64 and Office 2007.

    I get an errror in this part of the code:

    If Part1Document.InsertPages(numPages – 1, Part2Document,
    0, Part2Document.GetNumPages(), True) = False Then

    Any idea?

     

    Arne Grunert

  3. Perfect! This was the part of code I was looking for.
    My code added the PDF’s in the wrong order.

    Thanks!

     

    Jens

  4. Is supposed to be all 1 line, not 2.

     

    bla

  5. I don’t think that makes a difference – the interpreter will put the line back together.

     

    khk

  6. I don;t find acrobat in references. I only find Acrobat Type library.

     

    Vishy

  7. What version of Acrobat are you using?

     

    khk

  8. I have tried to use the above code in an office 2003 macro, with Adobe 8 with no success.

    I had to change the open statements to openAVDoc statements otherwise excel crashes when trying to open the file. After this, the number of pages is not calculated as the result for numPages is still zero eventhough the doc has 32. This could be why the InsertPages line fails, but I cant seem to get the correct information in order to complete this step.

    In addition, can you simply open an excel or word file in acrobat and have the conversion take place at that time?

     

    Mike

  9. Thanks for this great intro,

    this even works with Excel2000 from VBA (Vb6…) if you select Acrobat.tlb (“Acrobat 7.0 Type Lib..” / “Acrobat”).
    Now, reading / searching for Text patterns in a pdf via script shouldn’t be that great problem anymore, or should it? ;D

     

    MeAndI

  10. Define “great problem” :) It’s not easy to get text information out of a PDF document. You can use the JavaScript bridge and use the word finder in JavaScript to get access to the text.

     

    khk

  11. Hello, thank you for this code very useful. i would like to add page number at the bottom of my PDF created. Do you know how can i do this in VBA?

     

    heidi

  12. You can add page numbers via the VB2JS bridge by adding a form field or a text box and filling it with the page number. As the last step you can then flatten the page to “burn in” the interactive content you just added in order to convert it to real PDF content. Let me know if you need more detailed instructions.

     

    khk

  13. Thank you. It took me all day to find this information. It looks like this will only work if you have full Acrobat. What happens if I write code and the people using my code only have Acrobat Reader?

     

    reg

  14. Adobe Reader supports only a small number of functions. You need to be familiar with Adobe’s API documentation to determine what’s available in Reader vs. the full Acrobat. If somebody only has Reader installed, your program will not work, because the functions available to Reader are in a separate type library.

     

    khk

  15. Thanks a lot for this page…and its author
    It was really helpful :-)

     

    Gautier thomas

  16. K H – !
    You are VALUABLE!

    and lucid as well
    thanks,

     

    Norman Dolph

  17. This is my first time visiting your blog, and I want to commend you on writing a great article. This is a good foundation for manipulating PDF’s via MS Office, and I learned a lot reading this. I hope you write about this more…

     

    Greg

  18. Thanks for the code.Working great.

     

    K.Nagarjuna Reddy

  19. If Part1.pdf has only one page, and Part2.pdf has only a page, too, apears the message “Cannot insert pages”. I appreciate your help to resolve that situation.

     

    Carlos Alberto

  20. This has is great, it really fast tracked me into the world of Adobe Acrobat remote controlled by VBA. Many thanks! Rather than insert my focus is to replace some text in the PDF. Any suggestions?

     

    Roger

  21. Have been upgraded to Win7, Access 2007 and Acrobat 9. The code previously worked fine under XP, Access 2003 and Acrobat 7. Now when it gets to

    Part1Document.Open (pdfsrc)

    Access completely crashes. No error message. I tried stepping through the code and that is as far is it gets before crashing.

    Note: pdfsrc = “\\Discimageserver\PDFfiles\UGStats0.pdf”

     

    Steve

  22. Very nicely written. The information was very helpful.
    Thanks for posting!

     

    Craig

  23. Is it possible to convert PDF FILE TO TIFF FILES using VB 6.0?. It is urgent.

     

    Akhilesh Tripathi

  24. Akhilesh,

    VB6 is too old – it’s no longer supported by Adobe and the current version of Acrobat. With the Acrobat 9 and Acrobat X you can use the JSObj.saveAs() method to save a PDF document as one or more TIFF files.

     

    khk

  25. Is there any way to flatten a PDF from Excel?

    I’m filling a PDF form by creating a FDF file, once I finish I need to flatten the PDF form.

    I don’t have Acrobat, there are a couple of free libraries but mostly support VB.Net, not VBA.

    Thanks in advance,

    Luis G

     

    Luis Gonzalez

  26. Luis,
    You could install the free pdftk and then call that command line application via your VBA program. Pdftk can merge FDF data into a PDF file and flatten the document afterwards: http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/

     

    khk

  27. You rock, Karl! Thanks!

     

    Stephen Baer

  28. Is there a simple way to populate pdf fileds from VBA I have 5 and i8 versions of acrobat

     

    Craig Lytle

  29. Thanks for this great resource! I am wondering- Would there be a way to set this macro to loop through two separate folders of PDFs and merge them? The files have the exact same file names, but are just in two different folders. I would need the files with the same name merged together and then saved in a new location. Thanks!

     

    Jhull

  30. This is really a great article, with supporting documents to boot!
    I’ve looked through the docs and can not find any mention of programmatically setting a password for the pdf. Is this possible? I’ve been beating my head against a wall trying to figure it out using acrobat 8 standard and MS Access 2007. I realize this is an older post but if you happen to revisit it and see this, please help.

    Thanks,
    Dave

     

    Dave S

  31. This is so wonderful! You have saved me so much time.

     

    Henry Young

  32. Hi i use AcroExch.AVDoc to create a PDF and then display it in browser. But for this it requires a user to be logged into the Server(where the code is deployed). Otherwise it doesn generate. Am i missing something here?

     

    Niranjan

  33. Works like a Charm. Thank you.

     

    PDiez

  34. Is there any way to convert this to use in Access instead of Excel

     

    Vickie

  35. This works in Access too. I use it and it has been very helpful. I click a button on a form and call it from the code’s click event. If you need a sample of how I utilized and set up the code, I can provide it, but don’t know the appropriate procedure, is that to post here or to send to you directly via email.

     

    Steve

  36. Yea I got it to work in Access just had to add a another reference I just added the Acrobat I needed to add the one that included the libraries.
    Now I have another issue I need to add footers the file after it merges, do you think you can help me.

     

    Vickie

  37. Steve if you could please email me the code you have for windows 7, acess 2007 and acrobat 9 that is what I have on my new computer, I am working currently on my old and have it working there but if the code is different for the new please send to me or post here it doesn’t matter to me.

     

    Vickie

  38. Does anyone have a answer for my question above about the footers.

     

    Vickie

  39. Hi Vickie,

    The code is pretty much the same, I only encountered a problem after I switched to Windows 7 and Office 2007, but mysteriously, a day later everything worked fine. Currently I am using Windows 7, Office 2010 and Acrobat 9. Main thing is to be sure are the references. I’m using Adobe Acrobat 9.0 Type Library. The main changes I made to the code were for combining several pdf files into one pdf file. As for footers, although can be done not sure about the coding. I put footers in the access report rather than trying to add it later. If you would like the code or assistance my email address is sschechner@usfca.edu

     

    Steve

  40. Thanks, but the reason I need to add footers to the pdfs is because they are drawings, not a access report.

     

    Vickie

  41. Vickie,
    you can add footers via JavaScript (and the VGA/JS Bridge). It’s not as straight forward as one would hope, but it works: You would add a form field at the location where you want your footer to be, then you fill in the form field with the footer text (and this can be different for the different pages in your document), and as a final step, you flatten the pages in your document to “burn in” the otherwise interactive form field. You just have to be careful if you want other interactive content in your document, that you flatten the footers before you add any other fields that should remain interactive.

    Let me know if you have any questions regarding the actual mechanics of doing this.

    Thanks for reading and participating on my blog.

     

    Karl Heinz Kremer

  42. Not sure how that all works, but the pdfs are drawings That I need to use depending on what I am working on they get certain footers. So the only thing I would be adding is one field to these drawings. Now I am wanting to do this all this through Ms Access. Can you supply me with details of how to set it up and where I can get the coding from.

     

    Vickie

  43. Vicki, try this to get a string on the first page:

    Dim AcroApp As Acrobat.CAcroApp
    Dim jso As Object
    Dim rect(0 To 3) As Integer

    Dim theDocument As Acrobat.CAcroPDDoc
    Dim numPages As Integer

    Set AcroApp = CreateObject(“AcroExch.App”)
    Set theDocument = CreateObject(“AcroExch.PDDoc”)
    theDocument.Open (“C:\temp\Part1.pdf”)
    numPages = theDocument.GetNumPages()
    Set jso = theDocument.GetJSObject
    rect(0) = 72
    rect(1) = 72
    rect(2) = 72 * 4
    rect(3) = 108

    Dim field As Object
    Set field = jso.AddField(“theField”, “text”, 0, rect)
    field.Value = “this is some text”
    field.textSize = 12
    field.textColor = jso.Color.blue
    field.fillColor = jso.Color.ltGray

    jso.FlattenPages

    If theDocument.Save(PDSaveFull, “C:\temp\modified2.pdf”) = False Then
    MsgBox “Cannot save the modified document”
    End If

    theDocument.Close

    AcroApp.Exit
    Set AcroApp = Nothing
    Set theDocument = Nothing

     

    khk

  44. Thanks alot Karl

    This is what I have now

    a problem with now

    Set field = jso.AddField(“theField”, “text”, 0, rect)
    field.Value = “this is some text”

    the field.Value = “this some text” doesn’t work so a ignored it

    And ran the program and it crashed and pointed to the “theField” code

     

    Vickie

  45. Karl,

    Thanks alot it works only on the first page, now I need to get it to go on the rest of the pages and have control on where it lands and change the color and the grey background and font styles and I think I am in business. Any help would be much appreciated.

     

    Vickie

  46. Karl,

    I can not figure out what numbers to plug in to get the text in the right spot. Can you explain to me how the following works

    rect(0) = 72
    rect(1) = 72
    rect(2) = 72 * 4
    rect(3) = 108

    When I change the first 3 to get in close to where I want it center then 4 th line doesn’t work to get lined up on the bottom. Maybe if I understood moor about it I could get it to work.

    Do you know how to get the text to show up on the rest of the pages?

    All it’s greatly appreciated.

     

    Vickie

  47. Hi Vickie,

    this is where Acrobat’s JavaScript API documentation becomes a must-read: The JSO object is the JavaScript Object bridge, and on the “other” side of the bridge is what used to be JavaScript objects and methods. This means that in order to use the bridge, you need to be familiar with the JavaScript DOM. Take a look at the document that I link to in the original post.

    The “rect” is a pair of coordinates that describe the field: lower left corner and upper right corner. The units are in points (which is 1/72″). And, to get to other pages, you would change the 3rd argument of AddField: That’s the page number (starting at page 0).

    Let me know if you need anything else.

     

    khk

  48. Hello Karl,

    Thanks for all the help. Still not sure how to get it land in the right spot. I needed it in the center, bottom of a 11 x 17 paper. But I played with it and got it to work with the following code:
    rect(0) = 2
    rect(1) = 2
    rect(2) = 1225
    rect(3) = 50

    Dim field As Object
    Set field = jso.AddField(“theField”, “text”, 0, rect)
    field.Value = “this is some text added some more”
    field.textFont = “Arial”
    field.textSize = 15
    field.textColor = jso.Color.BLACK
    field.alignment = “center”
    field.alignment = “bottom”

    As far as getting it on the other pages I guess I will have to have it run in a loop as it counts the pages and put a page number field in the place where the page number goes.

     

    Vickie

  49. Let’s say your page is 17″ wide, and you want to add the field at the bottom. As I said, the coordinates are expressed in points, so the left most x coordinate would be 0, and the right most would be 72*17=1546. If you want your field 1/2″ high, that would be 36pt, and let’s say you want the lower edge of your field 1/4″ off the bottom, that would be 18pt. Let’s now combine all this. The lower left corner has an x value of 0, and a y value of 18, the upper right corner has an x value of 1546 and a y value of 18+36=54, so we would use the following code to express that:
    rect(0) = 0
    rect(1) = 18
    rect(2) = 1546
    rect(3) = 54

    As you’ve correctly identified, if you want the text in the middle, you need to use the center alignment.

    You can actually query a page for it’s size by getting the media box. With that information, you could write your routine so that it’s flexible as far as the actual page size goes.

     

    khk

  50. Karl,

    Thanks for explaining the formula, I can now figure out where to lay my text for the 11 x 17 and I also have some that I need to do on 8 1/2 by 11. And I can either tell it the size I am using or use the media box to figure it out for me.
    You have been a greet help.
    I have a lot of work ahead of me to get my project done. I was just trying to figure out if I can do some things before I start on it. I have a big Database already in place that is why I am trying to do everything from Access. Being able to merge the PDFs and then add a footer (adding text is just as good) was my goal.

     

    Vickie

  51. I am very new to VBA and would really like to learn more. I found this website and have added the button to my workbook. It works flawlessly! And I appreciate your explaining the code not just posting the code.

    I have a workbook that had 9 tabs. In VBA, I save 7 of the tabs as .pdfs after I have updated the data. Now, I’ve added this code to take the 7 .pdfs and merge them into 1 to send as a report.

    I’m wondering how to merge more than 2 files.
    I’m certain it’s a simple snippet, but I’m not that savvy yet.

    Thanks in advance.

     

    DTTODGG

  52. You would do that just like the case with two files, just with a loop to add not just one file to the first file, but all remaining files.

     

    khk

  53. Karl,

    Just wanted you to know thanks to this web site I have been able to accomplish a lot with my database. It works wonderful. I have hundreds of pdfs I have to work with now my program will do it for me, as where before I was doing it all manually.

    Thanks for sharing.

     

    Vickie

  54. Karl,

    Can you help me with another problem non related to this.

    In access with VBA I am trying to solve the problem below.

    I need to change all instances of the character A based on the next two characters. There might be more than 1 A so I have to change all instances. If the A is following by a single digit and followed by a another A or nothing it needs to be the A needs to be replaced by A0. If the A is followed by two numerial digits nothing changes. This needs to be done to as many A’s in the string. Need help. Thanks

     

    Vickie

  55. I’ve been able to use the jsobject in a VB console application (I’m using Visual Studio). However the jsobject doesn’t seem to be included in the Adobe Acrobat 9.0 Type Library, or any other type library that I can find. Is there a type library for this object?

     

    ASB

  56. the following sub is called from another Excel VBA sub that loops thru a folder to build a list of PDF filenames with their corresponding page numbers.
    “Part1Document.Open (sFilePathName)” invokes the following error msg:
    ‘——————————
    Run-time error ‘-2147417848(80010108)’
    Automation error:
    The object invoked has disconnected from its clients.
    ‘—————————-
    I hit the Debug button and excel crashes. any sugestions to fix this? I’ve double checked the filename and paths and they are correct. the code previously worked and one day I started recieving the error code
    using excel version 12.0 on windows 7.
    using Acrobat 10.0
    Sub PDFxPg(sFilePathName, xPgNo)
    Dim AcroApp As Acrobat.CAcroApp
    Dim Part1Document As Acrobat.CAcroPDDoc
    Set AcroApp = CreateObject(“AcroExch.App”)
    Set Part1Document = CreateObject(“AcroExch.PDDoc”)
    Part1Document.Open (sFilePathName)
    xPgNo = Part1Document.GetNumPages
    Part1Document.Close
    End Sub

     

    Ray

  57. Vickie,

    PDF is what I call a “final file format” – which means that it is very hard (sometimes almost impossible) to modify. The VB environment does not provide any tools to modify PDF content at all. You would have to use an Acrobat plug-in to get access to the APIs that would allow you to get access to that level of functionality (but even in a plug-in, it would be very complicated to do). It’s much easier to make that change in the original application.

    Hope that helps,

    Karl Heinz

     

    khk

  58. ASB, the type library is definitely part of Acrobat 9. Are you using the full version of Acrobat or the free Reader? It is not part of the Reader.

     

    khk

  59. Ray,
    I am sorry, but I am not a VB or VBA expert. I know how to come up with a sample program to show a certain feature, but only if things just work :) Maybe somebody else here knows how to fix your problem.

     

    khk

  60. Ray
    Are you working on a server and has the file name or anything changed. If on a server are you mapped still to that drive or the folders changed names.

     

    Vickie

  61. Hi Ray,

    You may have already found and fixed your problem but I had a similar experience with MS Access (2007) and Acrobat Pro 8 with this line:

    Part1Document.Open (sFilePathName)

    Previously, this code had worked fine for me (thanks khk!), and then it just stopped working. I have a work laptop and have no control over other program installations and I thought a forced update of Adobe Reader (9.5) had corrupted the library. I reinstalled Acrobat Pro 8 and still had the same crash in Access.

    I finally got it working by using this code:

    Dim Acroapp As CAcroApp
    Dim oDoc As CAcroAVDoc

    Set Acroapp = CreateObject(“AcroExch.App”)
    Set oDoc = CreateObject(“AcroExch.AVDoc”)
    oDoc.Open (“C:\apps\example.pdf”, “example”

    I haven’t a clue how or why this works but just in case it helps.

    KHK – Thanks for this blog – it has really helped with other projects!

     

    Chris

  62. I have been trying to merge two pdf files using Access 2007 (the two files were generated by Access reports). The problem I have is that the ‘Activex component cannot create object’. When it comes to the line

    Set AcroApp = CreateObject(“AcroExch.App”)

    It produces the runtime error 429.

    I am only new to this and somehow think it might be the Acrobat type library is in ‘ticked’ in tool referneces, but I have included all acrobat libraries I could find. Including Adobe Acrobat type 8.0 type library.

    Any help would be apprecaited.

     

    PeeBee

  63. Ray,

    I have a programming background like yours, an wrote several VBA for excel applications. I need to read PDFs into worksheets.

    I am trying to learn with your examples that I find very instructive, you should write a book for O’REILLY !

    How to I get the Acrobat library, to select it in the VBA’s Tools> References Window. Do I have to install an Adobe product ?

     

    Sylvain

  64. Hi Karl,

    I see you reply everyone, this is my situation. I already automated pdf´s documents using a database filled by a webpage.

    The thing is that I want to use only one adobe acrobat licence on a server so I can fill my files on the server. My issue begins in how to give the parameters from php, as a command line to read it as variables on my acrobat VB code.

    Example: The user must give is ID and his password, and give the date parameters from where to where fill his files, so this would be on php and it will be read on Acrobat VB code.

    Can i get some help please?

     

    Julio González

  65. Julio,
    You cannot use Acrobat on the server! For both technical, but also legal reasons. Adobe Acrobat is written for a user, sitting at the monitor and interacting with the application. When you run it on the server, a popup window could block the whole operation. But, more importantly, the EULA that you agree to when you install the software does not allow server use. So, what you want to do is both not technically feasible, but also illegal. There are other solutions that you can use: You need something that can fill a form. Take a look at the ABCPDF.Net library – you can use it directly from your VB.NET application. There are other solutions available, but – as I mentioned – before – I do like ABCPDF.Net. It’s easy to use and very powerful.

     

    khk

  66. Sylvain,
    You need to buy Adobe Acrobat. It’s part of the Acrobat product.

     

    khk

  67. PeeBee,
    Sorry, I don’t know enough about VBA to provide any solution to your problem. Have you tried to do the same e.g. in Word or Excel to see if your application is working in other environments?

     

    khk

  68. khk,

    Thanks for having a look at it. After more investigations I think that I would need acrobat professional for it to work (just an assumption because I don’t have a copy to test out the idea). I finally got around my dilemma by posting the information into a Word document and then saving the document as a PDF.

    Once again thanks for having a look.

     

    PeeBee

  69. PeeBee,
    you can test Adobe Acrobat for free for 30 days: There is an evaluation download available from Adobe’s web site.

     

    khk

  70. Thanks again Karl, I didn’t know about the evaluation download. That would make testing the issue easier.

    Regards PeeBee

     

    PeeBee

  71. Hi Karl,

    I’m also using Access VBA to modify PDF forms. My code is similar as yours and it works fine except:
    field.textColor = jso.Color.BLACK
    It goes through the code smoothly, but the field color of the output PDF does not change to black. I’m wondering if you know anything about it?

    Thanks a lot!

     

    Ariel

  72. Hi Karl,

    Just to correct something in my last comment:
    only assigning value to the field works, the others do not work (but go through the code with no problem in debug mode). It seems that I could only fill the form, but cannot modify it… I’m wondering if there is some setting of the pdf file I need to change?

    field.Value = “this is some text added some more” ‘works
    field.textFont = “Arial” ‘does not work
    field.textSize = 15 ‘does not work
    field.textColor = jso.Color.BLACK ‘does not work
    field.alignment = “center” ‘does not work
    field.alignment = “bottom” ‘does not work

    Thanks again!

     

    Ariel

  73. Please keep rambling, Karl. This was great!

     

    BAM

  74. Hi @ all,

    can you help me.
    I want to change via VBA batch PDFs.
    Replace last page works great, but I’m stil searchl a function
    which can select text in a PDF document and replace the text selection. “find and replace” function

    I use Visual Basic 6.0, and I find no function which helps me,
    did you have an idea?

    Thanks in advance

     

    TimoO

  75. Sorry to disappoint you, but there is no search and replace in Acrobat. PDF is kind of a “final” format, if you need to do things like replace text, you should go back to the original file (e.g. Word or Framemaker) and do all your work there, then export again to PDF. There are PDF editors that let you do some of that work, in PDF, but Acrobat is not one of them.

     

    khk

  76. Suggestion

    Is there any code to collecting PDF Documents book mark into different names like 12345 and combining into single PDF document ?

     

    Pavan

  77. Set AcroApp = CreateObject(“AcroExch.App”) gives an error that object can not be created.

    I guess, it is because i have only Adobe ReaderX only on my machine.

    I want to open a pdf, select the conents (i.e. Select ALL and the copy) and apste somewhere.
    I searched all forums but coulnot find a way which works with Adobe Reader.
    Can you please help.

     

    sanjib

  78. can you add a bookmarks?

     

    Jace

  79. Nice article. It’s a great jumping off point for more complex code.

    As for Find and Replace, TimoO might do a web search on the Redact functionality of Acrobat. As you state, Acrobat lacks a F&R function for several reasons, including security, but redacting might be a practical work-around if the circumstances are right.

    Thanks!

     

    Martin Petrey

  80. Mr. Kremer:

    I found this post to be extremely helpful. For that, thanks!

    In working to transform your basic code into something that fits my own needs I have developed the Excel VBA code below.

    All seems fine except I get an “Object variable or With block variable not set” error on the line that begins with ” If Document(0).InsertPages(numPages(0)…” below.

    I know you do not profess to be a VBA expert, but can you tell what might be wrong?

    Thank you.

    Sub AssembleDocs(DocsToAssemble As Variant, NewFile As String)
    Dim i As Integer
    Dim numPages(10) As Long
    Dim AcroApp As Acrobat.CAcroApp
    Set AcroApp = CreateObject(“AcroExch.App”)
    For i = 0 To UBound(DocsToAssemble) – 1
    ReDim Document(i) As Acrobat.CAcroPDDoc
    Set Document(i) = CreateObject(“AcroExch.PDDoc”)
    If Document(i).Open(DocsToAssemble(i)) = False Then
    MsgBox “Could not open ” & DocsToAssemble(i) & “.”
    AcroApp.Exit
    Set AcroApp = Nothing
    Exit Sub
    End If
    numPages(i) = Document(i).GetNumPages()
    ‘MsgBox numPages(i)
    Next i

    For i = 0 To UBound(DocsToAssemble) – 1
    If Document(0).InsertPages(numPages(0) – 1, Document(i + 1), 0, numPages(i + 1), True) = False Then
    MsgBox “Could not insert the specified pages.”
    End If
    Next i

    If Document(0).Save(PDSaveFull, NewFile) = False Then
    MsgBox “Could not save the extracted pages.”
    End If

    For i = 0 To UBound(DocsToAssemble)
    Document(i).Close
    Set Document(i) = Nothing
    Next i

    AcroApp.Exit
    Set AcroApp = Nothing

    End Sub

     

    Martin Petrey

  81. Karl,

    I’ve just read your article and intend using it to try something with Access 2010 VBA.

    I’m looking at wanting to embed a stamp (so probably a JPG image) into an existing PDF. We have hundreds of construction/fabrication drawings that our Document Control department wants us to stamp and sign. I would rather do that electronically than manually on hard copies.

    Be interested in any advice you may have.

    Regards,

    Craig

     

    Craig Windram

  82. Hi,
    I am unable to find Acrobat in the VBAProject , please guide me what to do

     

    Mani

  83. Mani, from the vba editor, click Tools, References. Select Adobe Acrobat X.x Type Library. Click OK. You will need Acrobat Pro already installed to do this

     

    Stephen Baer

  84. Hello,
    I am getting “Compile Error” for the following code
    ——————————————————–
    If Part1Document.InsertPages(numPages – 1, Part2Document,
    0, Part2Document.GetNumPages(), True) = False Then
    ————————————————————
    and the codes looks in Red color font
    Please help me to correct it
    Thanks and Regards
    Mani

     

    Mani

  85. Hi Karl ,

    I am trying to add custom properties in a PDF document using vba , can you please direct me in right direction , so that i can accomplish my goal .

    Thanks !
    Ankit

     

    Ankit

  86. […] […]

     
  87. this is my VBA code

    Dim AcrobatDocument As Acrobat.CAcroAVDoc
    Set AcrobatDocument = CreateObject(“AcroExch.PDDoc”)

    After writing to the fields I try

    AcrobatDocument.Save(PDSavecopy, Path)

    I’m getting syntax error when I compile.
    Any thoughts on why ?

     

    Joe

  88. also I use the result of the function

    Test = AcrobatDocument.Save(PDSavecopy, Path)

    or If AcrobatDocument.Save(PDSaveCopy, Path) Then

    I get the error method or data member not found and it highlights the save

     

    Joe

  89. I am successfully opening a PDF file from Excel and I want to execute in Adobe Reader or Professional, via VBA code in my Excel macro, the commands to Find (CTRL + F) and then Paste (CTRL + V) from cell “S1″ in which I have my search data. EVERYTHING WORKS FINE but the CTRL + F and CTRL + V. Any guidance you can give me would be greatly appreciated. Thanks in advance.

     

    Richard P

  90. Hello Mr. Kremer,
    I’m not sure whether you are german….

    Im looking for code to find and exchange text in pdf.
    I am able to find text (using findText) , but how to replace it?
    Do you have a suggestion?

    Thank you, best regards, Jens H. Geyer

     

    Geyer

  91. Hallo Jens, Suchen und Ersetzen ist in VB leider nicht moeglich. Nur Acrobat Plug-ins haben Zugriff auf die Datenstrukturen im PDF file, dies dies ermöglichen.

     

    khk

  92. Danke, Herr Kremer.

    Danke.. , suchen und ersetzen ist in VBA nicht möglich..

    Ich habe daher gerade eine Möglichkeit mit Java vor Augen, wo mit Hilfe einer Bibliothek namens PDFBox suchen und erstezen möglich ist.
    Der Plan ist dieses Programm aus VBA aufzurufen.

    Danke Ihnen, VG Jens H.G.

     

    Jens

  93. This was a good write-up, thank you.

     

    Chris

  94. I want to effectively replace MS Word’s MailMerge feature and use VBA and Acrobat forms instead.
    All of my client’s computers will have Acrobat Pro installed. However, the PDF files will be going out to their customers who will complete the forms with Acrobat Reader.

    I must do validation of the form fields and I believe I will need javascript to perform this vital function.

    My question is: will the embedded javascript code work with the Reader ?

     

    MSimms

  95. Can someone please give me a working example of using Access VBA to add text watermark to a pdf file? I have searched for over 20 hours now, internet, SDK documentation, etc, and am coming up empty. The code I have below throws a Object variable not set error on the jso.addWatermarkFromText line.

    Public Sub AddText()

    Dim pdApp As Acrobat.AcroApp
    Dim pdDoc As Acrobat.AcroPDDoc
    Dim pdPage As Acrobat.AcroPDPage
    Dim jso As Object

    Set pdApp = CreateObject(“AcroExch.App”)
    Set pdDoc = CreateObject(“AcroExch.PDDoc”)

    pdDoc.Open (“c:\Test\Test.pdf”)
    Set jso = pdDoc.GetJSObject
    jso.addWatermarkFromText (“Watermark Text”)

    pdDoc.Save 1, “c:\Test\Test.pdf”
    pdDoc.Close
    Set pdDoc = Nothing
    Set pdApp = Nothing
    MsgBox “Done”

    End Sub

     

    Jackie Davis

  96. I am greatful for the awesome content regarding VBA and Acrobat on this site. I have come across a hurdle that I haven’t been able to clear. I used some of the examples here and within the scriptiong guide and I am able to create a button in the pdf, but so far I haven’t been apply a .setaction to open a file without getting an error. I am working from Excel VBA

    Dim AcroApp As Acrobat.CAcroApp
    Dim jso As Object
    Dim rect(0 To 3) As Integer
    Dim theDocument As Acrobat.CAcroPDDoc

    Set AcroApp = CreateObject(“AcroExch.App”)
    Set theDocument = CreateObject(“AcroExch.PDDoc”)
    theDocument.Open (“C:\Users\Tom\Desktop\TEST.pdf”)

    Set jso = theDocument.GetJSObject
    rect(0) = 72
    rect(1) = 72
    rect(2) = 72 * 4
    rect(3) = 108

    Dim field As Object

    Set field = jso.addField(“actionField”, “button”, 0, rect)
    field.strokeColor = jso.Color.black
    field.fillColor = jso.Color.ltGray
    field.BorderStyle = jso.Border.b
    field.buttonSetCaption (“Push Me”)
    ‘>>> either of these lines below give a compile error expected = <<<

    field.setAction("MouseUp", "app.beep(0);" )
    field.setAction("MouseUp", app.openDoc("C:\\Users\\Public\\Pictures\\Sample Pictures\\creek.jpg", this))

    If theDocument.Save(PDSaveFull, "C:\Users\Tom\Desktop\TEST.pdf") = False Then
    MsgBox "Cannot save the modified document"
    Else
    MsgBox "done"
    End If

    theDocument.Close

    AcroApp.Exit
    Set AcroApp = Nothing
    Set theDocument = Nothing

    Any ideas for what I am missing?
    Thank you

     

    Tom

  97. With a bit more trial and error, I placed “set g =” in front of the setaction commands and I got it to pass the information to the PDF, but this just creates a javascript that doesn’t want to open the file because of security.

    What I REALLY would like to do is to create the button object as if I was doing it manually

    Button Properties
    Select Trigger – MouseUp
    Select Action – Open a File (.jpg)
    Path to a File

    Alternately, if I could have a PDF with the buttons already in it and edit the Path as needed …

    Am I out of luck?
    Thank you

     

    Tom

  98. how can we save the pdf with extended reader features?

     

    sami

  99. You need Adobe Acrobat for that, or the LiveCycle Reader Extensions server software.

     

    khk

  100. I know. I have acrobat. But how can I specify that when using .save

     

    Sami

  101. To explain more. I want to automate the creation of a PDF from an existing excel file and add signature fields. This is doable but the final PDF cannot be signed in reader it needs to be saved with extended features.

     

    Sami

  102. I have have unique issue. The code works fantastic on most of my computers, but a couple give an error message when running the save. The err.description is “Type Mismatch” The laptops are running MS Access 2007 on XP machines. I had six machines loaded with the same image. four work fine, two stop at the same place. Here is where it stops:
    If myMainDocument.Save(PDSaveFull, MyPath & MyPDF & “.pdf”) = False Then
    MsgBox “Cannot save the modified document”
    End If
    Thank you for the great code!!!

     

    Michael

  103. […] che tu non fai: MergePDF – Page 2 questo: Acrobat SDK, HowTo insert pages inside pdf oppure questo: Adobe Acrobat and VBA – An Introduction | Karl Heinz Kremer's Ramblings questo: VBA Merging PDF's (Acrobat 9.0) – Access World Forums Spero che uno di questi possa […]

     
  104. Hello, good to see this! It helped me A LOT.
    But I want to ask that, how should I write code in VBA in order to combine diffrent types of files into one pdf? Should I firstly convert every single one of them into pdf first? Or I can do it just in one shot?

    If so, How can I write the code? I mean, if there any function I can use to create a pdf from any kind of files?

    BTW, I have a well-licensed Acrobat. Many Thanks!!!!

     

    Xueying

  105. How should I write the code on the InsertPages section to insert ONLY page two of the Part2Document after the last page of Part1Document?
    I tried messing with the parameters but it keeps pulling the entire Part2Document document.

    If Part1Document.InsertPages(numPages – 1, Part2Document,
    0, Part2Document.GetNumPages(), True) = False Then
    MsgBox “Cannot insert pages”
    End If

     

    Jim

  106. Seems to me there are two ways you can achieve this:

    1. Insert the entire Part2Document into Part1Document, and then delete the Part2Document pages you don’t want, or;

    2. Extract page 2 of Part2Document, save it to a temporary file, insert that file into Part1Document, then delete the temporary file.

     

    Martin Petrey

  107. […] […]

     

    Metadati da PDF a tabella Excel - Excel

  108. […] Adobe Acrobat and VBA – An Introduction | Karl Heinz Kremer's Ramblings I'd play with it on a blank database until you get it running. It locked up my computer for a bit. __________________ Version: Access 2010 […]

     
  109. Jim,
    I’ve found Karl’s info and the subsequent discussion quite helpful. I haven’t used any of it yet (so beware of my advice), but below is a suggestion to insert ONLY page two of the Part2Document after the last page of Part1Document.
    Good luck and thank you everybody, especially Karl.
    Jim FM

    If Part1Document.InsertPages(numPages – 1, Part2Document,
    1, 1, True) = False Then
    MsgBox “Cannot insert pages”
    End If

     

    Jim FM

  110. This is very interesting. Do you happen to have any code that would add a background to a pdf file? I actually need to remove the white border around a PDF that I saved from Excel. I can do this by setting the background color to black in Acrobat – but I need to automate the process. Can you help with some code to get me started?

     

    Steve

  111. Steve, I don’t think this can be done with VB or VBA. You don’t have access to the PDF content via the IAC (or the JavaScript) interface. Only plug-ins can do that. And that’s where it gets complicated :)

     

    khk