Reading PDF Form Fields with VBA

I’ve written about VBA and Acrobat JavaScript before, and I’ve also mentioned that you can combine VBA and JavaScript to access PDF form fields, but I still owe a sample for that. I had to answer another question today about how to exactly do that, so I whipped up a quick sample program that demonstrates the use of the JavaScript Object (JSO) to read and write AcroForm fields.

We start the same way as in my old VBA sample to create a VBA program that references the Acrobat TLB and to add a button to a document. When we now use the following script as the button handler, we can work with form fields:

Private Sub CommandButton1_Click()
    Dim AcroApp As Acrobat.CAcroApp
    Dim theForm As Acrobat.CAcroPDDoc
    Dim jso As Object
    Dim text1, text2 As String

    Set AcroApp = CreateObject("AcroExch.App")
    Set theForm = CreateObject("AcroExch.PDDoc")
    theForm.Open ("C:\temp\sampleForm.pdf")
    Set jso = theForm.GetJSObject

    ' get the information from the form fields Text1 and Text2
    text1 = jso.getField("Text1").Value
    text2 = jso.getField("Text2").Value

    MsgBox "Values read from PDF: " & text1 & " " & text2

    ' set a text field
    Dim field2 As Object
    Set field2 = jso.getField("Text2")

    field2.Value = 13   ' assign the number 13 to the fields value

    ' get the information from the form fields Text1 and Text2
    text1 = jso.getField("Text1").Value
    text2 = jso.getField("Text2").Value

    MsgBox "Values read from PDF: " & text1 & " " & text2


    Set AcroApp = Nothing
    Set theForm = Nothing

    MsgBox "Done"
End Sub

This program requires a PDF file with text fields called “Text1” and “Text2” to be stored as C:\temp\sampleForm.pdf. With the explanation in the previous two blog posts, it should not be hard to understand what’s going on here. The only new command introduced is the getField() function, which returns a form field. The form field object has a property “value” which contains the actual value that’s assigned to the field. Give it a try and let me know how it works for you. The updated form field is not saved (because the document does not get saved) – I’ll leave that up to the reader to figure out.

Also, this program will not work with XFA forms (the ones you create in Designer). For those, you need to use the XFA DOM to access the form data. For anybody interested in XFA forms, the LifeCycle Designer ES Scripting Reference is a must read.

This entry was posted in Acrobat, JavaScript, PDF, Programming and tagged , , , , . Bookmark the permalink.

35 Responses to Reading PDF Form Fields with VBA

  1. Wayne says:

    Hi there,

    This is exactly what I am looking for. Thanks so much.
    However, my assignments are a bit different. I am wondering if you can give me a hand.

    The data source:
    1. An excel spreadsheet storing the raw data
    2. A PDF file with an interactive form used to store the data input by the user according to the above excel spreadsheet

    My assignments:
    1. input the raw data from excel spreadsheet to the PDF interactive form
    2. double check if the data input in the PDF interactive form is correct.

    I am not allowed to convert the Excel spreadsheet to the PDF file directly as the PDF file is the template with precise paragraphing and wording embedded. It is a heavy job when there are over hundreds of number. I am thinking if the excel VBA can do both assignments automatically or at least double check my input.


  2. Wayne says:

    Hi khk,
    I have adobe 9.1 professional and excel 2007, adobe TLB added.

    F8 stepinto F8 found “Runtime error ‘424’, object required”
    debug at this code ” text1 = jso.getField(“Text1″).Value”

    don’t know why?


  3. Wayne says:

    oh, “t” not “T”, i got it.
    btw, we can use the call function to save the updates.
    call theForm.Save(PDSaveFull,”C:\temp\sampleForm.pdf).

  4. Matt Conklin says:

    How do you test to make sure that the field exists in the form. I don’t want an error message when the user selects the wrong .PDF form to import into the spreadsheet.

    “On Error goto 0” does not trap the error.

    Is there a method such as FieldExists(“text1”) that I could use to trap that error before using the getField() method.

  5. admin says:

    I don’t know about VB (remember, I said that I am not working in VB). You can do test if a field exists in JavaScript by testing the value returned by getField():

    var f = this.getField("test");
    if (f == null)
    app.alert("f does not exist");

  6. Daryl says:

    In Excel 2010 VBA, I am trying to create a series of PDF forms each individually filled out but then combine them into a single PDF. I’ve tried a number of things but I keep getting stuck on the single PDF creation. Here’s what I currently have. The individual forms get created and saved with the correct data. However when I try to build the single PDF, it has the correct number of forms but the data is all filled in identically. This is the same problem that happens if you try to combine like forms into a single PDF in Adobe.

    In Adobe Acrobat you can avoid that problem by “flattening” the individual PDF files so that the form field names are removed and the data in the form cannot be edited. After that is completed, you can combine the individual PDF files and the field data is not overwritten. Is there a way to mimic that in Excel VBA? I actually do not even need to save the individual forms — only the single “combined” PDF. I only did that in this code because I thought it might help (it didn’t).

    I’d certainly appreciate any help or insight on this.

    Sub Process_Manual_Input()

    bProcessSuccessful = False

    sDefName = “ABC Fund – Forms 62 (xx.xx.xx).pdf”
    vFileName = Application.GetSaveAsFilename(sDefName, “PDF Files (*.pdf), *.pdf”)

    If vFileName = False Then
    sPath = Left(vFileName, Len(vFileName) – 4)
    sFile = Right(vFileName, Len(vFileName) – InStrRev(vFileName, “\”))
    MkDir sPath
    End If

    Set oAcroApp = CreateObject(“AcroExch.App”)
    Set oEmbeddedPDF = CreateObject(“AcroExch.PDDoc”)
    Set oManipPDF = CreateObject(“AcroExch.PDDoc”)
    Set oTempDoc = CreateObject(“AcroExch.PDDoc”)
    Set oFullPDF = CreateObject(“AcroExch.PDDoc”)

    ‘ Open Form 62 template

    szO = oEmbeddedPDF.Open(wsRequest.Range(“File62”).Value)
    ‘ Set oManipAV = oEmbeddedPDF.OpenAVDoc(“Form 62”)

    With wsManual
    lRow = .Range(“HeadingRow”).Row + 1
    lEndRow = .UsedRange.Rows.Count
    lNumPages = 0
    lNumFullPages = 0

    Do Until lRow > lEndRow

    szO = oManipPDF.Create
    szO = oManipPDF.InsertPages(-1, oEmbeddedPDF, 0, 3, 1)

    Set oManipAV = oManipPDF.OpenAVDoc(“Form 62”)

    Set oAcroForm = CreateObject(“AFormAut.App”)
    Set oAcroFields = oAcroForm.Fields

    oAcroFields.Item(“sFundName62”).Value = sFName
    oAcroFields.Item(“sStreet62”).Value = sStreet
    oAcroFields.Item(“sCityStateZip62”).Value = sCitStZip
    oAcroFields.Item(“sEIN62”).Value = sEIN

    For Each oAcroField In oAcroFields
    oAcroField.IsReadOnly = True

    sSecName = Trim(Replace(.Cells(lRow, mSecName), “/”, “-“))

    s62FileName = sPath & “\(” & sSecName & _
    ” – ” & Trim(CStr(.Cells(lRow, mSecID))) & “) ” & sFile

    szO = oManipPDF.Save(1, s62FileName)


    oTempDoc.Open (s62FileName)

    lNumPages = oTempDoc.GetNumPages()

    ‘ szO = oManipPDF.Save(1, sPath & “\(” & sSecName & _
    ‘ ” – ” & Trim(CStr(.Cells(lRow, mSecID))) & “) ” & sFile)

    If lNumFullPages > 0 Then
    szF = oFullPDF.InsertPages(lNumFullPages – 1, oTempDoc, 0, 3, 1)
    lNumFullPages = oFullPDF.GetNumPages()
    ‘ lNumFullPages = lNumFullPages + lNumPages
    ElseIf lNumFullPages = 0 Then
    Set oFullAV = oFullPDF.OpenAVDoc(“Full 62”)
    szF = oFullPDF.Create
    szF = oFullPDF.InsertPages(-1, oTempDoc, 0, 3, 1)
    lNumFullPages = oFullPDF.GetNumPages()
    ‘ lNumFullPages = lNumPages
    MsgBox “Error Writing to Full PDF”
    Exit Sub
    End If


    lRow = lRow + 1

    If oFullPDF.Save(1, sPath & “\” & sFName & ” 62s.pdf”) = False Then
    MsgBox “Save Failed”
    End If

    bProcessSuccessful = True

    End With

    Exit Sub

    MsgBox Error$
    On Error Resume Next
    Set oAcroApp = Nothing
    Set oManipPDF = Nothing
    Set oEmbeddedPDF = Nothing
    Set oManipAV = Nothing
    wbManual.Close False
    Set wsManual = Nothing

    Application.ScreenUpdating = True

    End Sub

  7. Karl Heinz Kremer says:

    Yes, you can flatten with VBA – but you need to use the JSObject interface and then call jso.flattenPages(). Take a look at this post, which explains how the JSO gets used:

    Here is information about the flattenPages method:

  8. Charles says:

    Does anybody know how to read the document restricions summary by excel vba?

    I get an error when certain document restictions are set, but I cannot figure out if they are set.

    For example, I am looking for how to read:
    “Printing” is “allowed”
    “Changing the document” is “allowed”
    and Document assembly, Content copying, content copying for accessibility, page extraction, commenting, filling the form fields, signing, creation of template pages

  9. jaime says:

    Using an excel macro, I need code to find text strings in the pdf and hilite the text or add comments/annotations to multiple strings that are listed in column A.

  10. Karl Heinz Kremer says:

    This is impossible to do in VBA, you would need to use the JSObject to do most of the work in JavaScript, and even there, it would be a very complex task.

  11. Pingback: Read Pdf fields

  12. Kelsey says:

    Hi, I am using VBA to add a combobox to a pdf document. Do you know of a way to add the properties to the combobox? I would appreciate any help!

    Dim App As CAcroApp
    Dim PDDoc As CAcroPDDoc
    Dim jso As Object
    Dim i As Long
    Dim FileName As String
    Dim field As Object
    Dim rect(3) As Integer

    Set field = jso.addField(“Performance”, “combobox”, 0, rect)

    Set jso = PDDoc.GetJSObject

    rect(0) = 182 ‘ x lower left
    rect(1) = 762 ‘ y lower left
    rect(2) = 297 ‘ x upper right
    rect(3) = 742 ‘ y upper right

    i = PDDoc.Save(PDSaveIncremental, FileName)
    End If

  13. Karl Heinz Kremer says:


    there are a number of problems with your code. You are using rect and so before they are defined. There is an “End If” without an “if”, and there are things missing at the beginning to open a PDDoc.

    Try something like this:

    Dim App As Acrobat.CAcroApp
    Dim PDDoc As Acrobat.CAcroPDDoc
    Dim jso As Object
    Dim i As Long
    Dim FileName As String
    Dim field As Object
    Dim rect(3) As Integer
    Dim items(3) As String

    App = CreateObject("AcroExch.App")
    PDDoc = CreateObject("AcroExch.PDDoc")

    jso = PDDoc.GetJSObject

    rect(0) = 182 ' x lower left
    rect(1) = 762 ' y lower left
    rect(2) = 297 ' x upper right
    rect(3) = 742 ' y upper right

    field = jso.addField("Performance", "combobox", 0, rect)

    field.strokeColor =
    field.fillCOlor = jso.color.yellow

    items(0) = "One"
    items(1) = "Two"
    items(2) = "Three"

    i = PDDoc.Save(1, "C:\temp\test-out.pdf")

  14. Kelsey says:

    Thank you for your help!

  15. todd says:

    We have been following your posts with some success – so many thanks. We did encounter one issue scripting acrobat from ms access/vba. Essentially we are unable to set a zip code without the (something) stripping the leading zero:

    objJSO.getfield(“Zip Code”).Value = “02110”

    In our use case the form is already pre-filled and we are just updating the zip. If we try something along the lines of:

    objJSO.getfield(“Zip Code”).Value = “David”

    we get a type error. So we are assuming that the Field object returned by getfield is typed somehow… We are looking for a solution (to maintain the leading zero), whether we need to change types or a new approach. Any help / information / ideas is greatly appreciated.

    Thank you

  16. haloween says:

    Here’s one a little far off. Is there a way to add a value to Sharepoint Document Library in specific column beside just added PDF document? I can add PDF docs to Sharepoint but just cant find a way to add some metadata to it. I was trying with PDF document properties, but didnt get very far.
    thanks for any hints…

  17. Karl Heinz Kremer says:

    Sorry, don’t know anything about Sharepoint.

  18. agustin cereceres says:

    Hi, I am new to PDF fillable form universe so I’m a little lost. I have an Excel spreadsheet where I have simple data and I would like to create this PDF form where in the first column the user types an item code and the other fields get automatically filled in, you can easily do this in Excel with a Vlookup. How can I translate that into PDF? Also this form is going to be mobile, users will have the form in their cellphones or tablets, so I assume the database has to be there in the background or hidden. Your help will be much appreciated. Thank you.

  19. Karl Heinz Kremer says:

    Augustin, you would have to implement this in JavaScript. I would use an array with the first selected item as the index. JavaScript allow you to use not just numeric values as indices, but other “things” as well (e.g. strings). This way, you can lookup the selection of values that you need to assign to your other fields.

    When you are planning on using such a form on a mobile device, you will have to select the mobile viewer that you require first and then code for that particular viewer. Mobile PDF viewers have a lot less functionality than the desktop versions. Make sure you select one that actually supports JavaScript, and then stick to the supported methods.

  20. Hayden says:

    Hi, I am trying to get data from a PDF file into Excel using VBA. Have tried a number of different types of code and methods, running into problems.

    Planning to use this in combination with Outlook so that when a PDF invoice is received, the attachment will automatically be downloaded to a folder then the key data from the PDF will be saved on a new row in an Excel spreadsheet.

    It can be done manually, saving the PDF to Excel, then copying and pasting in various elements, so it should be able to be done using VBA. However I am struggling.

    Currently getting “Error 1001: NotAllowedError: Security settings prevent access to this property or method” from this line of code:
    jsObj.SaveAs NewFileName, “com.adobe.acrobat.spreadsheet”

    Have tried also saving as text, which would still be OK.

    Can anyone help me please?

    Thanks in advance

  21. Karl Heinz Kremer says:

    Hayden, without knowing more about your setup, it’s impossible to say what’s going on. Can you save your PDF file as an Excel file manually in Acrobat via File>Save As Other>Spreadsheet>… ? Keep in mind that this works only with Adobe Acrobat, not the free Reader. You may also want to check the security settings of your PDF file. If the file does not allow content extraction, this will not work.

  22. Branddan says:

    Is it possible to fill a fillable pdf form without Acrobat? I have only the Adobe Reader installed and the code above doesn’t work (obviously..)

  23. Branddan says:

    Nevermind, I have found this:
    and it works just fine:)

  24. Ken says:

    Do you have any tutorials on accessing the XFA DOM to get form data similar to this application? I’ve been try for a while missing objects.

  25. Karl Heinz Kremer says:

    No, I don’t have anything about the XFA DOM. You should however – with the XFA documentation – be able to figure out how to access information in the xfa object.

  26. Michelle M. says:

    Thank you so very much for all of your samples. They’ve been a lifesaver! I am using Excel VBA to populate fields on a form in Adobe Pro XI. It works wonderfully. In some instances, however, I need to hide a checkbox (Check_box20) on the form. I can’t quite figure out how to use VBA to make this happen. Any ideas?

  27. Igor N says:

    This is an excellent post! Thank you so much for all your wisdom!

    One question I have. I have a pdf with a bunch of text fields. How do I get the names of all text fields in a pdf?

  28. Karl Heinz Kremer says:

    Igor, you can use a loop like this:

    for (var i=0; i
  29. wayne says:

    Hi Karl,

    I used your code to populate the PDF file successfully. The PDF file may not be created by Designer howeever. I recently had a PDF file created by designer (I guess because the ‘adds and edits the field’ option under form is gone and ‘edit in designer’ shows up when I open this file). The original macro then does not work. It says object required when running to the row jso.getField. For sure, the macro does work well when I open another PDF file which has the ‘adds and edits the field’ from form drop down list menu.

    Do you know why?
    Thanks a lot in advance.

  30. Karl Heinz Kremer says:

    Wayne, PDF forms created with LiveCycle Designer (or XFA forms) are not AcroForms. What I described here only works with AcroForms. You can potentially also fill in XFA forms, but for that you need to use a very different approach. You will have to manipulate the xfa data structure directly. Look at this discussion for some background:

  31. wayne says:

    Hi Karl,

    Thanks a lot. Let me read through the contents in the link.


  32. EconStudent says:

    Dear Karl,

    I’ve thinking about a way to extract info from pdf for some time and now I’m trying to use the above code.
    After adding the Acrobat and Adobe references in VBA I get an error on line
    Set AcroApp = CreateObject(“AcroExch.App”)

    ActiveX component can’t create object.

    Could you please point to me how to overcome this issue ?

    Many many thanks.

  33. dhiresh says:

    i have no of pdf files, in pdf file there is field name as ‘Name’, I want list of pdf file and value of name field in front of that pdf file.
    is it possible..

    a.pdf xxxxx
    b.pdf xxxxx

  34. Karl Heinz Kremer says:

    Dhiresh, You can certainly do that using VBA: Just loop through your list of files, and open one after the other in Acrobat and then extract the field value. I would then store the name of the file and the value in a list in VBA. Once you are done processing all your PDF files, just process the list that you’ve created and output the contents in the format you need. This is mostly straight VBA programming. If you are familiar with VBA, you should be able to write the loop and the list processing part without any problems, the Acrobat specific part you should be able to almost copy and paste from the code above.

  35. Pingback: Not seeing PDF fields from Excel VBA | news-rss feed form stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *