Fill PDF form in VBA code from XML file or XFDF file

Question

I am creating a solution in VBA code in MS Access where I populate a PDF form from data on the MS form the user is on. I have worked with both XML files to house the data for the fill as well as XFDF file.

The VBA fails when I try to fill an xfdf file. On the xmlRoot.selectSingNode set attempt, I get an error 91:

object variable or With block variable not set

On the other hand I have successfully create an XML file in this VBA code. I have taken into account the minor differences in node names between the XML file and the XFDF file.

Using the VBA for the XML file works, and if I open up the target FoxIt form in FoxIt, go to the "Import from Date File" button and select the XML I just created in MS Access VBA, it will fill the form nicely.

I want, however, for this to happen in VBA code. The goal is that the user is on the MS Form, clicks "create Docs" and is returned a series of nicely filled pdf forms. Here is the code:

Private Sub Command74_Click()

    Dim ClientName As String, ClientSSN As String, ClientState As String, 
    ClientZip As String
    Dim ClientAddress As String, ClientCity As String, pathToXML As 
    String, FileNameStr As String
    Dim xmlDoc As MSXML2.DOMDocument60, xmlRoot As MSXML2.IXMLDOMNode

    Dim AcroApp As Acrobat.AcroApp, Oripdf As Acrobat.AcroAVDoc

    Set xmlDoc = New MSXML2.DOMDocument60
    pathToXML = "C:\Users\Owner\Documents\SSA827TestAgain.xfdf"
    xmlDoc.Load (pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("fields").Item(0)

    ClientDOB = Me.DateofBirth.Value
    ClientName = Me.FirstName.Value & " " & Me.MiddleName.Value & " " & 
    Me.LastName.Value
    ClientCity = Me.City.Value
    
    xmlRoot.selectSingleNode("Birthday MMDDYYYY").Text = ClientDOB
    xmlRoot.selectSingleNode("NAME First Middle Last Suffix").Text = 
    ClientName
    xmlRoot.selectSingleNode("City").Text = ClientCity

    Call xmlDoc.Save(pathToXML)

End Sub

Here is the code for the XFDF file:

<?xml version="1.0" encoding="UTF-8"?>
<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">
  <f href="C:\Users\Owner\Documents\SSA827TestAgain.pdf"/>
  <fields>
    <field name="Birthday MMDDYYYY">
      <value/>
    </field>
    <field name="City">
      <value/>
    </field>
    <field name="Date Signed">
      <value/>
    </field>
    <field name="Guardian">
      <value>Off</value>
    </field>
      <field name="IF needed second witness sign here eg if signed with X above">
      <value/>
    </field>
    <field name="IF not signed by subject of disclosure specify basis for authority to sign">
      <value>Off</value>
    </field>
    <field name="NAME First Middle Last Suffix">
      <value/>
    </field>
    <field name="PLEASE SIGN USING BLUE OR BLACK INK ONLY INDIVIDUAL authorizing disclosure Signature">
      <value/>
    </field>
    <field name="Parentguardianpersonal representative sign here if two signatures required by State law">
      <value/>
    </field>
    <field name="Phone Number or Address">
      <value/>
    </field>
    <field name="Phone Number or Address_2">
      <value/>
    </field>
    <field name="Phone Number with area code">
      <value/>
    </field>
    <field name="SSN">
      <value/>
    </field>
    <field name="Signature">
      <value/>
    </field>
    <field name="State">
      <value/>
    </field>
    <field name="Street Address">
      <value/>
    </field>
    <field name="THIS BOX TO BE COMPLETED BY SSADDS as needed Additional information to identify the subject eg other names used the specific source or the material to be disclosed">
      <value/>
    </field>
    <field name="ZIP">
      <value/>
    </field>
    <field name="definition of disability and whether I can manage such benefits">
      <value>Off</value>
    </field>
    <field name="explain">
      <value>Off</value>
    </field>
  </fields>
  <ids original="D8695B945770BB45BD3C2557C3FC1A7C" 
       modified="B6283D8E6812C05FD77ED57AEB8D9375"/>
</xfdf>

Post the XFDF too. Afaik there is no way a node is named with a space in it. — Kevin Brown, Jul 01 '22 at 16:53
Just edited my post to include the XFDF code that I get when I "export" from the PDF form with blank fields. — Stanley Denman, Jul 01 '22 at 17:25
There are no *City*, *Birthday*, etc. nodes. These are values in *name* attribute on *field* nodes. Even then, you don't want to fill in the text of *field* but its next child node: *value*. — Parfait, Jul 01 '22 at 20:58
Does this answer your question? [Saving .xfdf as .pdf](https://stackoverflow.com/questions/30508966/saving-xfdf-as-pdf) — June7, Jul 01 '22 at 21:19
If SelectSingleNode takes xpath then you want SelectSingleNode("field[@name='Birthday MMDDYYYY']") you are trying to select a node named "Birthday MMDDYYYY" — Kevin Brown, Jul 01 '22 at 23:33

Parfait · Answer 1 · 2022-07-02T17:17:07.603

0

Consider looping on field nodes. Then, conditionally assign child value element by value of name attribute.

Sub Access2PDF()
On Error GoTo ErrHandle
    Dim xmlDoc As MSXML2.DOMDocument60
    Dim fields As IXMLDOMNodeList, field As IXMLDOMNode
    Dim pathToXML As String

    Set xmlDoc = New MSXML2.DOMDocument60
    pathToXML = "C:\Users\Owner\Documents\SSA827TestAgain.xfdf"
    xmlDoc.Load pathToXML
    
    Set fields = xmlDoc.getElementsByTagName("field")
        
    For Each field In fields
        Select Case field.Attributes.getNamedItem("name").Text
            Case "Birthday MMDDYYYY"
            field.selectSingleNode("value").Text = Me.DateofBirth.Value

            Case "City"
            field.selectSingleNode("value").Text = Me.City.Value

            Case "NAME First Middle Last Suffix"
            field.selectSingleNode("value").Text = _
                Me.FirstName.Value & " " & _
                Me.MiddleName.Value & " " & _ 
                Me.LastName.Value
        End Select
    Next field
    
    xmlDoc.Save pathToXML
    
ExitHandle:
    Set field = Nothing: Set fields = Nothing: Set xmlDoc = Nothing
    Exit Sub
    
ErrHandle:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    Resume ExitHandle
End Sub

edited Jul 02 '22 at 17:17

answered Jul 02 '22 at 00:12

Parfait

104,375
17
94
125

Thank you Parfait for this. When I run it, however, I get no errors by neither do I get an edits/changes in the target xfdf file. – Stanley Denman Jul 02 '22 at 12:44
code fails a getelementsbytagname - method or data member not found. I notice if I use autosuggest by period after 'field" it will not offer getelementsbytagname. – Stanley Denman Jul 02 '22 at 13:21
getelementsbytag name does not appear to be a property or method of IXMLDOMNode. I tried "firstChild" thinking you said we are after the "value" node which is a child but that did not work either. – Stanley Denman Jul 02 '22 at 14:08
See updated answer now fully tested with your sample XFDF. Add to `Select` for other fields. – Parfait Jul 02 '22 at 17:18
Thanks Parfait. I am so sorry to be a pain, but the code executes and xfdf file is unchanged. – Stanley Denman Jul 02 '22 at 18:50
I tried saving to a new xfdf doc, which happened, but no form values at all. – Stanley Denman Jul 02 '22 at 20:12
Are form controls actually entered? Try hard-coding values as a test in `.Text = ` assignments. – Parfait Jul 02 '22 at 20:21
I dim myvar as string, myvar = "01/27/1947", then "field.selectSingleNode("value").Text = myvar". Nothing in xfdf file. – Stanley Denman Jul 02 '22 at 20:54
Carefully, check the xfdf you actually use matches version posted here. In fact, test my full solution with xfdf sample here. XML is case sensitive. – Parfait Jul 02 '22 at 21:54
Oh I see. You think your code was not working because of xfdf version - interesting. Well l I again thank you for helping me. – Stanley Denman Jul 03 '22 at 01:53

score 0 · Answer 2 · edited Jul 02 '22 at 21:54

This works! Thanks to all for your help!

Private Sub Command74_Click()
On Error GoTo ErrHandle
    Dim xmlDoc As MSXML2.DOMDocument60
    Dim fields As IXMLDOMNodeList, field As IXMLDOMNode
    Dim pathToXML As String, myvar As String
    Set xmlDoc = New MSXML2.DOMDocument60
    pathToXML = "C:\Users\Owner\Documents\SSA827TestAgain.xfdf"
    xmlDoc.Load pathToXML
    xmlDoc.SetProperty "SelectionNamespaces", "xmlns:ns='http://ns.adobe.com/xfdf/'"
 
 
    Set fields = xmlDoc.selectNodes("//ns:field")
    
    For Each field In fields
            Select Case field.Attributes.getNamedItem("name").Text

            Case "Birthday MMDDYYYY"
            field.selectSingleNode("ns:value").Text = Me.DateofBirth.Value

            Case "Street Address"
            field.selectSingleNode("ns:value").Text = Me.MailingAddr1.Value & ", " & Me.MailingAddr2.Value
            
            
            Case "State"
            field.selectSingleNode("ns:value").Text = Me.State.Value
            
            Case "Zip"
            field.selectSingleNode("ns:value").Text = Me.Zip.Value
            
            Case "City"
            field.selectSingleNode("ns:value").Text = Me.City.Value
            
            Case "NAME First Middle Last Suffix"
            field.selectSingleNode("ns:value").Text = _
                Me.FirstName.Value & " " & _
                Me.MiddleName.Value & " " & _
                Me.LastName.Value
                
                
        End Select
    Next field
    
    xmlDoc.Save ("C:\Users\Owner\Documents\NEWSSA827TestAgain.xfdf")
    
ExitHandle:
    Set field = Nothing: Set fields = Nothing: Set xmlDoc = Nothing
    Exit Sub
    
ErrHandle:
    MsgBox Err.Number & ": " & Err.Description, vbCritical
    Resume ExitHandle
End Sub

Fill PDF form in VBA code from XML file or XFDF file

2 Answers2