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" (http://"C:\Users\dcothran\Desktop\New folder\Test.pdf") ...... look at the fourth answer down.


From Engineering World itself :

http://www.myengineeringworld.net/2013/03/vba-macro-to-convert-pdf-files-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