Solved

How do I write to an Acrobat XFA form from Access VBA

Posted on 2015-01-29
19
555 Views
Last Modified: 2015-02-25
I need help writing data out from an Access app to a fillable PDF form.
This is the top part of the form I’m trying to fill in. I’ve superimposed the field names over the text boxes.
 Picture of XFA form
My app has a reference to the Acrobat Type Library 9.0. I wrote this procedure to read the editable fields into an Access table, which worked…
 VBA Code to read fields from form (this works)
But when I tried to write values out to those fields, using the same GetFields method, it didn’t work…
VBA Code to write values to form fields (this doesn't work)
Debug Output:
Set field us-ids[0].sfpatitemflow[0].patItemsf[0].patnumbertxt[0] = 123 '123 is the value that should be inserted
jso.GetField(us-ids[0].sfpatitemflow[0].patItemsf[0].patnumbertxt[0]) = ‘nothing inserted
.
What I discovered from the Adobe SDK forum is that the form is an XFA form, not an AcroForm…

 Top part of document's XML source
…and the same methods don’t work. One of the Adobe MVPs recommended that I use the “xfa” property in the JSObject to write to the form, then said that I needed to understand javascript. That’s where I hit a wall.
0
Comment
Question by:DarrellDoesData
  • 11
  • 8
19 Comments
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
The following two different methods should work for reading the values:

text1a = jso.xfa.form.form1.subform.FirstName.rawValue()

text1b =
jso.xfa.ResolveNode("form.form1.subform.FirstName").formattedValue()

And, the following line should work to modify the field value:

jso.xfa.form.form1.subform.LastName.rawValue = "NEW VALUE"

These lines are copies (I just changed the two variable names for the first
two lines) from an Excel VBA project I worked on last year. I just checked
with the current version of Acrobat XI (and Excel 2010) and the code is
working and will extract and set information in an XFA form.

You will need to understand how data is stored in an XFA file in order to
access data in the XFA format.
0
 

Author Comment

by:DarrellDoesData
Comment Utility
I appreciate your response, Karl, but I'm still not understanding the syntax. What does "form.form1.subform" refer to -- nodes? There's no field called FirstName on the form, but there's a field called "us-ids[0].#pageSet[0].IDSMaster[0].idshead[0].appNumber[0]".
I tried every combination of this construct:
jso.xfa("us-ids[0].#pageSet[0].IDSMaster[0].idshead[0].appNumber[0]").rawValue()
...and got this error...
Object-Doesnt-Support.png
This construct didn't work either...
jso.xfa.us-ids[0].#pageSet[0].IDSMaster[0].idshead[0].appNumber[0]").rawValue()

BTW, I'm using Acrobat 9.0 Pro Extended
0
 

Author Comment

by:DarrellDoesData
Comment Utility
Sorry, sent previous message too soon.
The 2nd construct I attempted was as follows:
jso.xfa.us-ids[0].#pageSet[0].IDSMaster[0].idshead[0].appNumber[0].rawValue()
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
As I said, these lines were taken out of an old project, and therefore refer to fields in a different form. You will have to use the field names that are in your form. I assume that the field names you are referring to are the output of iterating over the fields using the AcroForm syntax. This will not work for XFA forms, you need to use the fields names as used in the XFA form. By looking at your sample name, I noticed that one hierarchy name contains a dash - that would not be valid syntax in VBA (or JavaScript). This means you will have to use the resolveNode method of referencing your fields.

As I've explained on the Adobe forums, you will have to use the output of the following JavaScript command to figure out the path you need to use:

this.xfa.data.saveXML();

Open in new window


This is not a simple task, and you may have to work through the documentation in the Acrobat SDK, plus the XFA specification to get this working.
0
 

Author Comment

by:DarrellDoesData
Comment Utility
I've requested that this question be deleted for the following reason:

The "Expert" basically advised me to read the reference guide. I don't consider that an answer.
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
Really, I only advised you to read the reference manual?!? I've given you sample code to retrieve values and to set values in XFA forms, I've also given you a command that allows you to analyze the data stored in the XFA file, which you will need to access the field data, and in addition to that, I also advised you to read the documentation, because no programming job can be completed by just copying and pasting information, you will have to understand what you are doing. The information I provided should give you a headstart in understanding the documentation.
0
 

Author Comment

by:DarrellDoesData
Comment Utility
OK, I'll narrow my objective down to one field on the form, called "appNumber". Here's a snapshot of the hierarchy:
XFA Hierarchy
XFA Hierarchy - XML snippets
I executed the following from inside VBA (the JS console was already open):

jso.console.println ("Application number = " + jso.xfa.resolvenode("form.us-ids.IDSMaster.idsHead.appNumber").formattedValue())

It threw Error 424 "Object required".

I tried executing this command from inside the JS Console:
 
console.println("Application number = " + this.xfa.resolvenode("form.us-ids.IDSMaster.idsHead.appNumber").formattedValue());

Open in new window


and got a "this.xfa has no properties" error. Obviously, I don't know how to load the PDF doc into the Console and define "this". If you could give me the javascript code to open the document and retrieve this single value that would be enough to get me over the hump.
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
Open your XFA form in Acrobat and then execute this command in the JS console:

this.xfa.data.saveXML();

Open in new window


What is the output?
0
 

Author Comment

by:DarrellDoesData
Comment Utility
this.xfa.data.saveXML();
<?xml version="1.0" encoding="UTF-8"?>
<xfa:data xmlns:xfa="http://www.xfa.org/schema/xfa-data/1.0/"
><us-ids date-produced="20050902" dtd-version="v20_EFSWeb" file="" lang="" status=""
><us-filing-info
><us-application-identification-info
><doc-number
>14297823</doc-number
><date
>2014-06-06</date
></us-application-identification-info
><us-first-named-inventor
><name name-type=""
>John Doe</name
></us-first-named-inventor
><file-reference-id
>7017.104</file-reference-id
><us-group-art-unit
>2833</us-group-art-unit
><primary-examiner
><name name-type=""
/><electronic-signature date="" place-signed=""
><basic-signature
><text-string
/></basic-signature
></electronic-signature
></primary-examiner
></us-filing-info
><us-ids-certification text="1"
><us-foreign-pat-office-citation text="0"
/><us-new-findings text="0"
/><us-certification-statement-attached boilerplate-text="0" file="" type=""
/><us-fee-submitted boilerplate-text="0"
/><applicant-name
><name name-type=""
>Jack D. Stone, Jr.</name
><registered-number
>38,324</registered-number
></applicant-name
><electronic-signature date="2014-11-18" place-signed=""
><basic-signature
><text-string
>/Jack D. Stone, Jr./</text-string
></basic-signature
></electronic-signature
></us-ids-certification
><us-patent-cite
><us-doc-reference id="dd:id_2" num="1" sequence=""
><doc-number
>0793399</doc-number
><name name-type=""
>Schultz, W.J.F</name
><kind
>A</kind
><date
>1905-06-27</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="2" sequence=""
><doc-number
>0867624</doc-number
><name name-type=""
>Warthen, H.J.</name
><kind
>A</kind
><date
>1907-10-08</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="3" sequence=""
><doc-number
>0937577</doc-number
><name name-type=""
>Crump, F.H.</name
><kind
>A</kind
><date
>1909-10-19</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="4" sequence=""
><doc-number
>1078007</doc-number
><name name-type=""
>Stange, Ottomar</name
><kind
>A</kind
><date
>1913-11-11</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="5" sequence=""
><doc-number
>1988813</doc-number
><name name-type=""
>Seguin, O.J.</name
><kind
>A</kind
><date
>1935-01-22</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="6" sequence=""
><doc-number
>2485280</doc-number
><name name-type=""
>Grace, J.F.</name
><kind
>A</kind
><date
>1949-10-18</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="7" sequence=""
><doc-number
>3563131</doc-number
><name name-type=""
>Ridley Sr., Robert L.</name
><kind
>A</kind
><date
>1971-02-16</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="8" sequence=""
><doc-number
>4111568</doc-number
><name name-type=""
>Wing, George S.  </name
><kind
>A</kind
><date
>1978-09-05</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="9" sequence=""
><doc-number
>4635337</doc-number
><name name-type=""
>Helderman, J. Frank</name
><kind
>A</kind
><date
>1987-01-13</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="10" sequence=""
><doc-number
>4784554</doc-number
><name name-type=""
>Break, Douglas G.</name
><kind
>A</kind
><date
>1988-11-15</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="11" sequence=""
><doc-number
>5030052</doc-number
><name name-type=""
>Anderson et al.</name
><kind
>A</kind
><date
>1991-07-09</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="12" sequence=""
><doc-number
>5860548</doc-number
><name name-type=""
>Kerr, Jr., Jack R. </name
><kind
>A</kind
><date
>1999-01-19</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="13" sequence=""
><doc-number
>5907124</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>A</kind
><date
>1999-05-25</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="14" sequence=""
><doc-number
>5965845</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>A</kind
><date
>1999-10-12</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="15" sequence=""
><doc-number
>6096974</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>A</kind
><date
>2000-08-01</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="16" sequence=""
><doc-number
>6204450</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>A</kind
><date
>2001-03-20</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="17" sequence=""
><doc-number
>6207898</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>B1</kind
><date
>2001-03-27</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="18" sequence=""
><doc-number
>6207989 </doc-number
><name name-type=""
>Li et al.</name
><kind
>B1</kind
><date
>2001-03-27</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="19" sequence=""
><doc-number
>6291768</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>B1</kind
><date
>2001-09-18</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="20" sequence=""
><doc-number
>6303859</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>B1</kind
><date
>2001-10-16</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="21" sequence=""
><doc-number
>6423899</doc-number
><name name-type=""
>Reiker, Kenneth H.</name
><kind
>B1</kind
><date
>2002-07-23</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="22" sequence=""
><doc-number
>6811364</doc-number
><name name-type=""
>Kelzer, Ken</name
><kind
>B2</kind
><date
>2004-11-02</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="23" sequence=""
><doc-number
>6910826</doc-number
><name name-type=""
>Damiano, Armand A.</name
><kind
>B1</kind
><date
>2005-06-28</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="24" sequence=""
><doc-number
>7985041</doc-number
><name name-type=""
>Lin, Jin-Jie</name
><kind
>B2</kind
><date
>2011-07-26</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_2" num="25" sequence=""
><doc-number
>8109785</doc-number
><name name-type=""
>Kidman, Brent L.</name
><kind
>B2</kind
><date
>2012-02-07</date
><class
/><subclass
/></us-doc-reference
></us-patent-cite
><us-pub-appl-cite
><us-doc-reference id="dd:id_3" num="1" sequence=""
><doc-number
>20020197132</doc-number
><name name-type=""
>Cruz et al.</name
><kind
>A1</kind
><date
>2002-12-26</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_3" num="2" sequence=""
><doc-number
>20040057811</doc-number
><name name-type=""
>Kelzer, Ken</name
><kind
>A1</kind
><date
>2004-03-25</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_3" num="3" sequence=""
><doc-number
>20060036259</doc-number
><name name-type=""
>Carl et al.</name
><kind
>A1</kind
><date
>2006-02-16</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_3" num="4" sequence=""
><doc-number
>20100086378</doc-number
><name name-type=""
>Lin, Jin-Jie</name
><kind
>A1</kind
><date
>2010-04-08</date
><class
/><subclass
/></us-doc-reference
><us-doc-reference id="dd:id_3" num="5" sequence=""
><doc-number
>20130215381</doc-number
><name name-type=""
>Raghuprasad, Puthalath K. </name
><kind
>A1</kind
><date
>2013-08-22</date
><class
/><subclass
/></us-doc-reference
></us-pub-appl-cite
><us-foreign-document-cite
><us-foreign-doc-reference id="1" num="" sequence="" translation-attached="no"
><country
/><doc-number
/><name name-type=""
/><date
/><class
/><subclass
/></us-foreign-doc-reference
></us-foreign-document-cite
><us-nplcit file="" id="1" medium="" num="" sequence="" translation-attached="no" type="" url=""
><text
/></us-nplcit
><version-info
>2.1</version-info
><clientversion
>9</clientversion
><numofpages
>6</numofpages
></us-ids
></xfa:data
>

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
As you can see, the field "appNumber" is not reflected in that XML data. The field is using a data binding, which makes things a bit more complex. However, when you analyze the data that is in the XML, you find that you can actually reference the application ID via this JavaScript construct:

MsgBox (jso.xfa.resolveNode("data.us-ids.us-filing-info.us-application-identification-info.doc-number").value)

Open in new window

0
 

Author Comment

by:DarrellDoesData
Comment Utility
Thank you, Karl. We are making some progress. This construct worked from the JS Console...
xfa.resolveNode("data.us-ids.us-filing-info.us-application-identification-info.doc-number").value;
14297823

Open in new window


I was also able to change the value...
xfa.resolveNode("data.us-ids.us-filing-info.us-application-identification-info.doc-number").value = "12345678";
12345678

Open in new window


Changed to...
Doc Number Changed from JS Console
But this did not work from VBA (both debug.print and console print threw the same error)...
Print DocNumber from VBA
Is there another way to pass the values to javascript and execute from VBA?

Also, I'm curious as to why the XDP hierarchy is completely different from the XML hierarchy?

Thanks!
Doc-Number-From-JSConsole.png
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
As to why the two are different - this is because of the data binding in the XFA form. I would not have done it this way, but I guess the document author had a good reason.

You can create a folder level script in Acrobat and then call that script from VB. Take a look at this blog post of mine that explains how to do that:

http://khkonsulting.com/2009/03/counting-bookmarks/

Are you working with the form from the government web site? I downloaded that and that's how I verified that my VBA code was working.
0
 

Author Comment

by:DarrellDoesData
Comment Utility
As to why the two are different - this is because of the data binding in the XFA form. I would not have done it this way, but I guess the document author had a good reason.

What is normally the difference between an XDP and the XML?

You can create a folder level script in Acrobat and then call that script from VB. Take a look at this blog post of mine that explains how to do that:

I will try that, thank you. Having never worked with JS before, this is a whole new learning experience for me.

Are you working with the form from the government web site? I downloaded that and that's how I verified that my VBA code was working.

Yes, it's the PTO form from their site, with some test data filled in. I've attached it here.
PTO-IDS-FillableForm.pdf
0
 

Author Comment

by:DarrellDoesData
Comment Utility
Another question...
I just tried the exact same script as before and now it's telling me that it's "undefined"...
xfa.resolveNode("data.us-ids.us-filing-info.us-application-identification-info.doc-number").value;

undefined

Open in new window


What happened?
0
 

Author Comment

by:DarrellDoesData
Comment Utility
I just tried the exact same script as before and now it's telling me that it's "undefined"...
I figured this one out. I inserted another command into the Console that threw an error and it went into debug mode. The Call Stack has to be cleared out before it'll accept another command.

Any idea why I'm getting the error in VBA?
Can't Print Doc Number from VBA
0
 
LVL 44

Expert Comment

by:Karl Heinz Kremer
Comment Utility
Actually, "undefined" is a good thing :)

Whenever you execute a script in the console, the return value will be written to the console. If you for example execute a "script" that contains just a number (e.g. "3"), the return value will be that number. When you execute "3 + 4", the result of that calculation will be returned and printed. Some commands don't return anything, for such a command, you would get an "undefined". The command "this.flattenPages();" will return "undefined" when executed, but it will definitely do it's job and flatten the document.

As for the "difference between XDP and XML": XML is a whole group of languages or syntax for languages. XDP is one specific implementation of XML. XDP is used to describe a XFA form in XML, you can write out the complete form as an XDP file, or a PDF file (and some other formats). That’s the complete form. When you use the "SaveXML()" call, only the data is written out, using a very simple flavor of XML. You get only the data, nothing else, no formatting information, no data binding information, nothing else but the data.  


Hope that helps.
0
 

Author Comment

by:DarrellDoesData
Comment Utility
Yes, thank you for the explanation.

More questions:
1. I read your blog on how to execute a JS function from inside VBA. I used the same syntax you used, wrapping it in a MsgBox command: MsgBox ReadIDS(). This worked, but it also pops up an empty MsgBox. Do you know of another way to execute the function from inside VBA?

2. Does the JS function have to be stored in C:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Javascripts? It pops up an Administrator warning, which I can Continue but when I write out the script to that location from code it throws a Permission Denied error.

3. I'll be passing in many different fields each time I produce the form. I can re-construct the script each time by writing out the entire text file complete with the ResolveNode commands, but is there an easier way to populate the variables?

Thank you.
0
 
LVL 44

Accepted Solution

by:
Karl Heinz Kremer earned 500 total points
Comment Utility
Oops, I think I sent the wrong link, but it sounds like you were able to find the correct posting. Just for reference purposes, it's here:

http://khkonsulting.com/2009/03/acrobat-javascript-and-vb-walk-into-a-bar/

You don't have to call the script via a message box, you should be able to just execute the command that provides the data for the message box like this:

Dim res as String
res = jso.CountAllBookmarks()

Open in new window


This will assign the output of the JavaScript function to a string.

Yes, the function has to be stored in the Acrobat JavaScripts directory, but there are two options: One on the system level, and one on the user level. Take a look here for more information: http://khkonsulting.com/2010/12/acrobat-javascripts-where-do-they-go/

As for an easier way to get the data from the JavaScript routine: You can pass in the path to the data object you want to retrieve from VB. It would look something like this (this is pseudo code, so it won't compile or execute):

VBA:

Dim dataPath as String
dataPath = "data.us-ids.us-filing-info.us-application-identification-info.doc-number"

' call JavaScript function
Dim res as String
res = ReadIDS(dataPath)

Open in new window


The JavaScript side would look like this:
function ReadIDS(dataPath) {
    return jso.xfa.resolveNode(dataPath).value;
}

Open in new window


This way, your Javascript routine would be able to work with any form and any path.
0
 

Author Closing Comment

by:DarrellDoesData
Comment Utility
I was ready to throw in the towel because I got no usable answer from Karl. But through persistent questioning he gradually spooled out enough bits of information that I was able to cobble together a solution. It wasn't the result I was hoping for, but I at least got something workable.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now