VBA to write PDF froms from Excel, Radio Buttons

[Iron_Ranger]

Hello all,

I am trying to make an excel table that can fill PDF forms for my job. I have manages to get it so that it works perfectly for filling in text fields, but I can't get the Radio Buttons to be checked, even when I know what the options are for the check boxes. I got the original code from another blog (posted at the bottom) and modified it to suit my needs. To give an example, the strFieldNames(2) below is a radio button that only has two options, YES and NO. YES is supposed to check one box, and NO the other. When I run the code, the fillable text is imported to the form, but the Radio buttons are unchecked, even when I know the data is in the column in Excel. Here is the code I am using:

Sub WritePDFForms()
 
    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
 
    strPDFPath = ThisWorkbook.Path & "\" & "Unlocked Structure Form.pdf"
    Set shMain = Sheets("Main")
       
      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]"
 
    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
 
        Set objAcroApp = CreateObject("AcroExch.App")
       
        Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
       
        '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
                   
            On Error GoTo 0
           
            'Create the output path, which will be like C:\Users\Christos\Desktop\Forms\01) First Name Last Name.pdf.
            With shMain
                    strPDFOutPath = "C:\Users\dcothran\Desktop\New folder\Test.pdf"
            End With
           
            'Save the form as new PDF file.
            objAcroPDDoc.Save 1, strPDFOutPath
   
            '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
   
End Sub

There is a lot more to the code, but I trimmed it down for clarity. If anyone can help me with this, I will be most grateful. Thanks!

Original code here: myengineeringworld.net/2013/10/read-and-write-pdf-forms-from-excel-vba.html

[Iron_Ranger]

Also, I forgot to mention it an Exclusion Group where only one box can be checked at a time.

[Iron_Ranger]

I partially figured it out. I is checking the boxes after all, but when it is saving the form, it is unchecking them for some reason. Anyone have any ideas? I can make it keep the data by disabling the "objAcroAVDoc.Close True" line from the script, but as soon as I close the PDF and reopen it the boxes are unchecked again.

[Logit]

.
.
This macro saves each sheet (can be edited) as a PDF file to my desktop (edit the path for your system). I placed a few radio buttons
and checkboxes on the first sheet and the macro saved them as being checked. Not certain if it will work for you but give it a try.

Maybe make a call to this macro within your existing one ?

Sub createPDFfiles()
    Dim ws As Worksheet
    Dim Fname As String
    For Each ws In ActiveWorkbook.Worksheets
        On Error Resume Next 'Continue if an error occurs


        ' Name PDF files based on the worksheet Index
        Fname = "C:\Users\My\Desktop\" & "Sheet " & ws.Index & " Report"


        ' If you want to name the PDF files differently just change the Fname variable above to
        ' whatever you like. For example if you changed Fname to:
        '
        '  Fname =  “C:\myFolder\pdfs\” & ActiveWorkbook.Name & "-" & ws.Name
        '
        '  The files would be stored in C:\myFolder\pdfs, and named using the
        ' spreadsheet file name and the worksheet name.
        '
        ' WARNING: Using worksheet names may cause errors if the  names contain characters that Windows
        ' does not accept in file names. See below for a list of characters that you need to avoid.
        '
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False
    Next ws
End Sub

What about saving the PDF as an image before saving it to

"C:\Users\dcothran\Desktop\New folder\Test.pdf"

You could save the image there instead ?

Here are some resources :

http://"C:\Users\dcothran\Desktop\New folder\Test.pdf" ...... look at the fourth answer down.


From Engineering World itself :

http://www.myengineeringworld.net/20...iles-into.html



You could also save as image, paste into a worksheet in your workbook for future reference.

[jackthelad]

Yup, that's where the riddle starts to me! I was actually trying to sort things out with writing xls-to-pdf forms by myself (that's the trick, I'm dum-dum with this and post here anyway, sorry), but I'm more used to do these things in way more easy-to-understand fashion. I still dn't give up nevertheless but also taking a glance at some easier tools to work with pdf forms like this one https://ds11.pdffiller.com and thinking what could be done here if there were no JS and stuff but just VBA. Sort of food for thought