Losing Data when saving to PDF from Excel Sheet

[Darth_Alicious]

Hello,

I have found a VBA script online for exporting data from a spreadsheet into a pre-made PDF document. For my job, we fill in a large number of these form, so this will help a lot. However, whenever I run the script and it produces the PDFs, certain fields are not filled, specifically the radio buttons, which work off of a single data field (i.e, if the field is "YES" then one is checked, if "NO" the other is checked). Here is the code I use (trimmed for size):

Sub WritePDFForms()      
    'Declaring the necessary variables.
    Dim strPDFPath              As String
    Dim strFieldNames(1 To 154) As String
    Dim i                       As Long
    Dim j                       As Integer
    Dim LastRow                 As Long
    Dim objAcroApp              As Object
    Dim objAcroAVDoc            As Object
    Dim objAcroPDDoc            As Object
    Dim objJSO                  As Object
    Dim strPDFOutPath           As String
    Dim shMain As Worksheet
    
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the path of the sample PDF form.
    strPDFPath = ThisWorkbook.Path & "\" & "Unlocked Structure Form.pdf"
    Set shMain = Sheets("Main")


    'Set the required field names in the PDF form.
      strFieldNames(1) = "FormData[0].Page1[0].CRtype[0]"
      strFieldNames(2) = "FormData[0].Page1[0].Original[0]"
      strFieldNames(3) = "FormData[0].Page1[0].FieldDate[0]"
      strFieldNames(4) = "FormData[0].Page1[0].FormDate[0]"
      strFieldNames(5) = "FormData[0].Page1[0].RecorderNo[0]"
      strFieldNames(6) = "FormData[0].Page1[0].Sitename[0]"
      strFieldNames(7) = "FormData[0].Page1[0].ProjectName[0]"
      strFieldNames(8) = "FormData[0].Page1[0].MultList[0]"
      strFieldNames(9) = "FormData[0].Page1[0].SurveyNo[0]"
      strFieldNames(10) = "FormData[0].Page1[0].NRcategory[0]"
     


    'Find the last row of data in sheet Write.
    With shMain
        .Activate
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    'Loop through all rows of sheet Write and use the data to fill the PDF form.
    
    For i = 3 To LastRow
    
        On Error Resume Next
        
        'Initialize Acrobat by creating the App object.
        Set objAcroApp = CreateObject("AcroExch.App")
        
        'Check if the object was created.
        If Err.Number <> 0 Then
            MsgBox "Could not create the App object!", vbCritical, "Object error"
            'Release the object and exit.
            Set objAcroApp = Nothing
            Exit Sub
        End If
        
        'Create the AVDoc object.
        Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
        
        'Check if the object was created.
        If Err.Number <> 0 Then
            MsgBox "Could not create the AVDoc object!", vbCritical, "Object error"
            'Release the objects and exit.
            Set objAcroAVDoc = Nothing
            Set objAcroApp = Nothing
            Exit Sub
        End If
        
        On Error GoTo 0
        
        'Open the PDF file.


        If objAcroAVDoc.Open(strPDFPath, "") = True Then
            
            'Set the PDDoc object.
            Set objAcroPDDoc = objAcroAVDoc.GetPDDoc
           
            'Set the JS Object - Java Script Object.
            Set objJSO = objAcroPDDoc.GetJSObject
            
            On Error Resume Next
            
            'Fill the form fields.
            For j = 1 To 154
                
                objJSO.GetField(strFieldNames(j)).Value = CStr(shMain.Cells(i, j + 1).Value)
                                             
            Next j
                        
            'Create the output path, which will be like C:\Users\Christos\Desktop\Forms\01) First Name Last Name.pdf.
            With shMain
            strPDFOutPath = ThisWorkbook.Path & "\8" & shMain.Cells(i, 155).Value & ".pdf"
            End With
            
            'Save the form as new PDF file.
            objAcroPDDoc.Save PDSaveFull, strPDFOutPath
            objAcroPDDoc.Save PDSaveFull, "Unlocked Structure Form.pdf"
    
            'Close the form without saving the changes.
            objAcroAVDoc.Close True
            
            'Close the Acrobat application.
            objAcroApp.Exit
               
            'Release the objects.
            Set objJSO = Nothing
            Set objAcroPDDoc = Nothing
            Set objAcroAVDoc = Nothing
            Set objAcroApp = Nothing
            
        Else
        
            MsgBox "Could not open the file!", vbCritical, "File error"
            
            'Close the Acrobat application.
            objAcroApp.Exit
            
            'Release the objects and exit.
            Set objAcroAVDoc = Nothing
            Set objAcroApp = Nothing
            Exit Sub
            
        End If
        
    Next i


    Application.ScreenUpdating = True
    
    
End Sub

The thing is, if I have the PDF open, I can see the radio buttons being checked, and if I disable the "objAcroAVDoc.Close True" line of code, they stay checked until I save and close the PDF. Then they are gone when I reopen it. Can anyone help me with this?

Thanks!

 

[Darth_Alicious]

On further inspection, I think I know why it is doing this, I just don't know how to fix it. The PDDoc Object is the data that is visible directly on the form, like text fields, where the AVDoc Object is hidden fields, like the Values behind the Radio Boxes. Is there a way to save the AVDoc object as well as the PDDoc?

 

[Darth_Alicious]

Also, I believe its an XFA document. When I try using the format for XFA (jso.xfa.Forms.FormData.Page1.Sitename.rawValue) I get an error "Object doesn't support this property or method".