Iron_Ranger
04-21-2017, 05:24 AM
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
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