Windows 7 64bit 
Access 2007
Hello all,
I have found a very useful script online for filling an existing PDF from an Excel Table. It works for the most part from Excel, but it would be even better if I could make it work straight from the Access Database I produce the excel table from. Here is the VBA Script I have:
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
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"
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")
'Create the AVDoc object.
Set objAcroAVDoc = CreateObject("AcroExch.AVDoc")
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
On Error GoTo 0
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
'Enable the screen.
Application.ScreenUpdating = True
'Inform the user that forms were filled.
MsgBox "All forms were created successfully!", vbInformation, "Finished"
End Sub
Is there any way to convert this to work for Access? I found the original script here: http://www.myengineeringworld.net/20...excel-vba.html
Also, the only thing it currently does not do is fill in check boxes or radio buttons. The buttons are in exclusion groups with YES and NO values assigned, but when I import the data into the form, the boxes are not checked. Any ideas why? This is more of a PDF question, but I have had such good luck here I thought I'd ask
Reply With Quote
