Export Access report to PDF Fillable Form

Question

Id like to populate an Adobe Fillable form using data pulled from an Access report. I would appreciate some advice on how to get this code to work. I am relatively new to VBA, so my biggest question would come from which part of the code are Adobe, and which part are from Access fields.

Some of my field names for access are: Payee Extended_Payee, Check Request Date, Invoice Number, etc. Some of my field names in the Adobe form are:Payee, Today's Date, Invoice Number, etc.

This is the coding I have so far - I would just like clarification on which part of the code should reflect the adobe fields and which part should be from Access :

   Private Sub Export_Click()

Dim FileNm, gApp, avDoc, pdDoc, jso

FileNm = "M:\Check_Requests\2017\PDF_Exports\CheckForm.pdf" 'File location
Set gApp = CreateObject("AcroExch.app")

Set avDoc = CreateObject("AcroExch.AVDoc")
FileCopy FileNm, newFileNm
If avDoc.Open(newFileNm, "") Then
    Set pdDoc = avDoc.GetPDDoc()
    Set jso = pdDoc.GetJSObject

    jso.getField("CheckForm[0].Page1[0].Payee[0]").Value = "Payees_Extended_Payee"
    jso.getField("CheckForm[0].Page1[0].Address[0]").Value = "Address"
    jso.getField("CheckForm[0].Page1[0].City[0]").Value = "City"
    jso.getField("CheckForm[0].Page1[0].State[0]").Value = "State"
    jso.getField("CheckForm[0].Page1[0].Zip_Code[0]").Value = "Zip_Code"
    jso.getField("CheckForm[0].Page1[0].Comments_to_Include_on_Remittance[0]").Value = "Description_of_Expense"
    jso.getField("CheckForm[0].Page1[0].Todays_Date[0]").Value = "Check_Request_Date"
    jso.getField("CheckForm[0].Page1[0].Invoice_Number[0]").Value = "Invoice_Number"
    jso.getField("CheckForm[0].Page1[0].Invoice_Date[0]").Value = "Invoice_Date"
    jso.getField("CheckForm[0].Page1[0].Total_Amount[0]").Value = "Total_Amount"
    jso.getField("CheckForm[0].Page1[0].Description_of_Expense[0]").Value = "Description_of_Expense"
    jso.getField("CheckForm[0].Page1[0].Other[0]").Value = "Other"
    jso.getField("CheckForm[0].Page1[0].GL1[0]").Value = "GL_Company"
    jso.getField("CheckForm[0].Page1[0].AU1[0]").Value = "Accounting Unit"
    jso.getField("CheckForm[0].Page1[0].A1[0]").Value = "Account"
    jso.getField("CheckForm[0].Page1[0].CODE1[0]").Value = "Project_Code"
    jso.getField("CheckForm[0].Page1[0].AMOUNT1[0]").Value = "Amount_Split_1"
    jso.getField("CheckForm[0].Page1[0].GL2[0]").Value = "GL_Company_2"
    jso.getField("CheckForm[0].Page1[0].AU2[0]").Value = "Accounting_Unit_2"
    jso.getField("CheckForm[0].Page1[0].A2[0]").Value = "Account_2"
    jso.getField("CheckForm[0].Page1[0].CODE2[0]").Value = "Project_Code_2"
    jso.getField("CheckForm[0].Page1[0].AMOUNT2[0]").Value = "Amount_Split_2"
    jso.getField("CheckForm[0].Page1[0].GL3[0]").Value = "GL_Company_3"
    jso.getField("CheckForm[0].Page1[0].AU3[0]").Value = "Accounting_Unit_3"
    jso.getField("CheckForm[0].Page1[0].A3[0]").Value = "Account_3"
    jso.getField("CheckForm[0].Page1[0].CODE3[0]").Value = "Project_Code_3"
    jso.getField("CheckForm[0].Page1[0].AMOUNT3[0]").Value = "Amount_Split_3"
    jso.getField("CheckForm[0].Page1[0].Total[0]").Value = "Amount_Total"
    jso.getField("CheckForm[0].Page1[0].Requestor[0]").Value = "Requestor"
    jso.getField("CheckForm[0].Page1[0].Approving Manager[0]").Value = "Approving_Manager"
    jso.getField("CheckForm[0].Page1[0].Extension[0]").Value = "Extension"
    jso.getField("CheckForm[0].Page1[0].Email_Address[0]").Value = "Email_Address"
    pdDoc.Save PDSaveIncremental, FileNm 'Save changes to the PDF document
    pdDoc.Close
    End If
'Close the PDF; the True parameter prevents the Save As dialog from showing
avDoc.Close (True)

'Some cleaning
Set gApp = Nothing
Set avDoc = Nothing
Set pdDoc = Nothing
Set jso = Nothing

End Sub

Alexander Ushakov Alexander Ushakov · Accepted Answer · 2017-06-09T12:48:20

Your code is a function that creates and runs instance of AcroExch.app, opens PDF document referenced by variable FileNm, finds form in it, fills it with values and than saves it. All code is executed by Access.

This is the "Adobe specific" code because it depends on API of AcroExch.AVDoc:

If avDoc.Open(FileNm, "") Then
    Set pdDoc = avDoc.GetPDDoc()
    Set jso = pdDoc.GetJSObject

    jso.getField("CheckForm[0].Page1[0].Payee_Extended_Payee[0]").Value = "Payee Extended_Payee"
    jso.getField("CheckForm[0].Page1[0].Address[0]").Value = "Mailing Address"
    jso.getField("CheckForm[0].Page1[0].City[0]").Value = "Mailing Address"
    jso.getField("CheckForm[0].Page1[0].State[0]").Value = "Mailing Address"
    jso.getField("CheckForm[0].Page1[0].Zip_Code[0]").Value = "Mailing Address"
    jso.getField("CheckForm[0].Page1[0].Description_of_Expense[0]").Value = "Comments to Include on Remin"
    jso.getField("CheckForm[0].Page1[0].Check_Request_Date[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Invoice_Number[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Invoice_Date[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Total_Amount[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Other[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].GL1[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AU1[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].A1[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AMOUNT1[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].GL2[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AU2[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].A2[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AMOUNT2[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].GL3[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AU3[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].A3[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].AMOUNT3[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Amount_Total[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Requestor[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Approving Manager[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Extension[0]").Value = "Installer_or_Owner"
    jso.getField("CheckForm[0].Page1[0].Email_Address[0]").Value = "Installer_or_Owner"
    pdDoc.Save PDSaveIncremental, FileNm 'Save changes to the PDF document
    pdDoc.Close
    End If
'Close the PDF; the True parameter prevents the Save As dialog from showing
avDoc.Close (True)

Values in fields are set by expression:

jso.getField("FormName[0].Page1[0].FieldName[0]").Value = NewValue

where FormName is the name of form in PDF document, FieldName - name of field in the form and NewValue - value to fill in this field.

Export Access report to PDF Fillable Form

1 Answers