Asteroid_Miniic
asked on
Fill in PDF form using MS-Access data
I'm trying to create a VBA routine to use my customer data in an MS-Access 2003 (mdb) database to populate a shipping request PDF form. The problem is that I don't know the names of the fields in the PDF form.
So first I need a utility VBA routine that will simply iterate thru all the fields in the form to retrieve their names.
I've seen plenty of examples on how to populate the fields using something like:
jso.getField("topmostSubfo rm[0].Page 1[0].field Name[0]"). value = "myValue"
where jso is the JSObject from PDDoc object from AVDoc object. There aren't any good coding examples using JSObject in VBA.
It seems the most closely guarded secret on the internet is how to use jso.getField("topmostSubfo rm[0].Page 1[0].field Name[0]") more dynamically so that I can iterate thru all the fields in a PDF form using VBA code in Access.
I work for the Navy (DoD) so my machine is locked down and I cannot install any third party software. I'm actually running Access 2010 but because the Navy has the machines so locked down and they really don't understand how to implement the Trust Center settings properly I have kept my db apps as .mdb to prevent security warnings and issues for my users.
Thank you for any suggestions and advice.
So first I need a utility VBA routine that will simply iterate thru all the fields in the form to retrieve their names.
I've seen plenty of examples on how to populate the fields using something like:
jso.getField("topmostSubfo
where jso is the JSObject from PDDoc object from AVDoc object. There aren't any good coding examples using JSObject in VBA.
It seems the most closely guarded secret on the internet is how to use jso.getField("topmostSubfo
I work for the Navy (DoD) so my machine is locked down and I cannot install any third party software. I'm actually running Access 2010 but because the Navy has the machines so locked down and they really don't understand how to implement the Trust Center settings properly I have kept my db apps as .mdb to prevent security warnings and issues for my users.
Thank you for any suggestions and advice.
I work in a similarly constrained environment. Our general approach to PDF forms is what Jim has suggested.
ie: Make a replica of the PDF form as an Access Report, and use the OutputTo command with acFormatPDF as the format parameter to export the report to PDF (and yes, this does work in Access 2010 with databases in .mdb format).
That said, to loop through fields in a PDF form, you should be able to treat it like any other collection in VBA.
I haven't tested this myself, but look at the lines labeled "borrowed code" in this Adobe Forums post:
http://forums.adobe.com/thread/1147373
I'm *guessing*, based on Access's fields collection, that this would also work for PDFs (assumption: you have already declared 'JSO"):
ie: Make a replica of the PDF form as an Access Report, and use the OutputTo command with acFormatPDF as the format parameter to export the report to PDF (and yes, this does work in Access 2010 with databases in .mdb format).
That said, to loop through fields in a PDF form, you should be able to treat it like any other collection in VBA.
I haven't tested this myself, but look at the lines labeled "borrowed code" in this Adobe Forums post:
http://forums.adobe.com/thread/1147373
I'm *guessing*, based on Access's fields collection, that this would also work for PDFs (assumption: you have already declared 'JSO"):
Dim fld
For Each fld In JSO.Fields
Debug.Print "Field Name: " & fld.Name & " Value: " & fld.Value
Next
Based on the path for your form fields, you are using XFA forms (forms created in LiveCycle Designer, not AcroForms). To get the list of fields, you have to do some extra work.
One approach is to open up your document in Adobe Acrobat, then open up the JavaScript console (Ctrl-J or Cmd-J), then execute the following line of code:
This will reply with an XML data structure that shows you all the fields:
Now you should be able to figure out what the field names are and how to access them.
One approach is to open up your document in Adobe Acrobat, then open up the JavaScript console (Ctrl-J or Cmd-J), then execute the following line of code:
this.xfa.data.saveXML();
This will reply with an XML data structure that shows you all the fields:
<?xml version="1.0" encoding="UTF-8"?>
<xfa:data xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/"
><form1
><Page1
><TextField1
/><TextField2
/><TextField3
>0</TextField3
></Page1
></form1
></xfa:data
>
Now you should be able to figure out what the field names are and how to access them.
ASKER
Thanks all, for your input. Sorry for the late response.
I cannot do an Access report and print to PDF printer (driver) because the PDF form is a standard shared form meaning we need to put our shipping data on the certain fields on the form and then the form is sent electronically to the shipping department and they fill in other fields on the form, which is then send to another organization who fills in the their part on the same form (the govt is a boon-doggle when it comes to processing anything--sigh).
Good news, I'm mostly there. I found a way to retrieve the field names from a PDF form and a way to populate the form with my Access data. I'm not at work right now, so I'll post the code later. I just need to figure a way to keep the blank form intact by keeping it hidden (protected) from users, and have each user using a copy as their own form. Not too hard.
I cannot do an Access report and print to PDF printer (driver) because the PDF form is a standard shared form meaning we need to put our shipping data on the certain fields on the form and then the form is sent electronically to the shipping department and they fill in other fields on the form, which is then send to another organization who fills in the their part on the same form (the govt is a boon-doggle when it comes to processing anything--sigh).
Good news, I'm mostly there. I found a way to retrieve the field names from a PDF form and a way to populate the form with my Access data. I'm not at work right now, so I'll post the code later. I just need to figure a way to keep the blank form intact by keeping it hidden (protected) from users, and have each user using a copy as their own form. Not too hard.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Private Sub test_Adobe()
Dim jsFile As String
Dim joApp As AcroApp
Dim joAVDoc As AcroAVDoc
Dim joPDDoc As Acrobat.AcroPDDoc
Dim joFormApp As AFORMAUTLib.AFormApp
Dim joFormFields As AFORMAUTLib.Fields
Dim joFormField As AFORMAUTLib.Field
On Error GoTo Error_Handler
jsFile = "C:\Users\someuser\Documents\Projects\EXWC_SHIPPING_REQUEST.pdf"
Set joApp = New AcroApp
Set joAVDoc = CreateObject("AcroExch.AVDoc")
If joAVDoc.Open(jsFile, "") Then
Set joPDDoc = joAVDoc.GetPDDoc()
Set joFormApp = CreateObject("AFormAut.App")
Set joFormFields = joFormApp.Fields
For Each joFormField In joFormFields
If joFormField.Type = "text" Then
If Left(joFormField.Name, 3) <> "TC " _
And Left(joFormField.Name, 6) <> "PRICE " _
Then
joFormField.Value = joFormField.Name '<-- this is the main line of code
End If
Else
If joFormField.Name = "FUNDS TYPE" Then
joFormField.Value = "NWCF"
End If
If joFormField.Name = "SHIPMENT MODE" Then
joFormField.Value = "Ground"
End If
End If
Next joFormField
jsFile = "C:\Users\someuser\Documents\Projects\EXWC_SHIPPING_REQUEST1.pdf"
joPDDoc.Save PDSaveFull, jsFile
joPDDoc.Close
End If
Exit_Handler:
joAVDoc.Close True
Set joPDDoc = Nothing
Set joAVDoc = Nothing
Set joApp = Nothing
MsgBox "Done processing"
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description, vbCritical, "Adobe Testing"
GoTo Exit_Handler
Resume
End Sub
I'm glad you resolved your issue. I'm going to add this to my personal knowledge base for future use.
Thanks for using EE.
Thanks for using EE.
ASKER
Jim P, you're welcome. Please share with anyone who gets stuck on this issue.
G
G
ASKER
Solves my problem.
Helpful suggestions . For my two cents if others are requiring a Taiwan Synology Developer Guide File Hosting Module , my family came across a fillable document here http://goo.gl/3zl7eV.
Is there anything stopping you from make your own Access report and then "print" it to a PDF and send it off?
Just a dumb thought.