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

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>

CodePudding user response：

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

CodePudding user response：

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