Solved How to automate "PDF Prepare form" for all Access text fields from VBA ? (1 Viewer)

[PatTahiti]

Hello,

My client generates a PDF file from an Access report.
This report has text fields that must be transformed into PDF form fields.
Actually this is done manually by the client : in Acrobat Pro -> prepare form => all access text fields are converted into pdf form fields.

I'd like to automate the action that consists to transform text fields into PDF form fields.

(Note : I can fill the fields from VBA, this is not a problem.)

Any idea ?

Thank you.
Regards, Patrice

 

[Gasman]

See Similar threads at the bottom of this thread

 

[PatTahiti]

Hi,

Are you sure ?
Wich one ?
I read them but I don't see the same question.

 

[theDBguy]

Hi. Quick question, wouldn't it be easier to simply prepare one master pdf form and simply auto fill it using Access? Just curious...

 

[PatTahiti]

Hi.

Hi. Quick question, wouldn't it be easier to simply prepare one master pdf form and simply auto fill it using Access? Just curious...

Hi,
The client generates about 50 Access reports that must be transformed into PDF forms.
I have automated the first step to generate PDF files from the Access reports.
Now, these PDF files must be transformed into PDF Forms.
All forms are different at each generation depending on data coming from the Access DB, so I can't create a master as you suggest, if I get your point correctly
Regards, Patrice

 

[theDBguy]

Hi.

Hi,
The client generates about 50 Access reports that must be transformed into PDF forms.
I have automated the first step to generate PDF files from the Access reports.
Now, these PDF files must be transformed into PDF Forms.
All forms are different at each generation depending on data coming from the Access DB, so I can't create a master as you suggest, if I get your point correctly
Regards, Patrice

Sorry, I don't understand. What do you mean by "different at each generation?" Are you talking about the design of your reports change in look depending on the data? I hope not. I hope you're only referring to the data are different each time a report is converted into a pdf. So, which one is it?

 

[The_Doc_Man]

Adobe offers a reader utility free of charge and if you had an Adobe form, the reader will let you activate the form options. However, the reader is not able to actually create the form in the way you describe. You need the more advanced Adobe program for that. The one that ISN'T free.

IF you have the advanced version of Adobe, you will have an Adobe .DLL file (or more than one) that has support for document building. If you have that, you can use VBA to open an Adobe application object and manipulate files. Typically when you have the .DLL for the utility, you can do almost anything from VBA that you could have done through the utility's graphic interface. Which means if you do it once by hand and take notes, you would then know what do in VBA by finding the corresponding controls. The .DLL abilities tend to parallel the graphic version of the utility.

If you DON'T have the advanced Adobe utility that is the document creator, then you probably CANNOT do what you are trying to do because you will only have the "reader" .DLL file.

I don't have the advanced utility so I cannot give you further advice on it.

 

[PatTahiti]

Sorry, I don't understand. What do you mean by "different at each generation?" Are you talking about the design of your reports change in look depending on the data? I hope not. I hope you're only referring to the data are different each time a report is converted into a pdf. So, which one is it?

Sorry if I was not clear.
The number of lines in the PDF form will change each time because the query in Access will retreive a different number of records when it is fired. The main layout does not change but 50 records of Access will generate 50 pdf fields and 100 records will generate 100, and so on.
Knowing that, I have to transform an undetermined number of text fields into pdf fields.
Regards, Patrice

 

[theDBguy]

Sorry if I was not clear.
The number of lines in the PDF form will change each time because the query in Access will retreive a different number of records when it is fired. The main layout does not change but 50 records of Access will generate 50 pdf fields and 100 records will generate 100, and so on.
Knowing that, I have to transform an undetermined number of text fields into pdf fields.
Regards, Patrice

I see. But, did you know there are fields in PDF that can grow. At least, that's what I remember (unless I'm remembering something else), I'll have to see if I can post a link about it.

In other words, if you're talking about the same multiple column data with varying number of rows, I think that's possible in a PDF field/table. I know InfoPath had those. I think you get a button like "Add more data..." Let me go check now...

 

[PatTahiti]

I see. But, did you know there are fields in PDF that can grow. At least, that's what I remember (unless I'm remembering something else), I'll have to see if I can post a link about it.

In other words, if you're talking about the same multiple column data with varying number of rows, I think that's possible in a PDF field/table. I know InfoPath had those. I think you get a button like "Add more data..." Let me go check now...

Well, I think my explanations are not clear.
1. I loop through the reports of my Access project -> OK
2. For each report found, I export to PDF -> OK
3. I would like to transform the text fields of the generated pdf into form fields -> NOT OK

actually the client does point # 3manually in Acrobat Pro

 

[theDBguy]

Well, I think my explanations are not clear.
1. I loop through the reports of my Access project -> OK
2. For each report found, I export to PDF -> OK
3. I would like to transform the text fields of the generated pdf into form fields -> NOT OK

actually the client does point # 3manually in Acrobat Pro

But my point was this:

1. Client creates a master PDF form
2. You loop through your reports in your Access project
3. For each report found - fill out the fields in the master PDF form and save a copy

The difference being, step 1 in my approach is performed by the client only one time. Whereas, step 3 in your approach requires the client to perform the same task as many reports and as many times you export a report to PDF.

So, in essence, you eliminate the last step in the current process, if we can create a master PDF that will work with all your reports. Imagine the work load difference of creating one master PDF compared to say 50 PDF reports that have to be converted to fillable forms - and then repeat the process again next time reports have to be generated from Access.

Anyway, I haven't had a chance to search for the "expandable" PDF form I was talking about earlier, so maybe my idea won't work anyway if that's not possible - but I could swear it's available.

 

[theDBguy]

Okay, a quick search led me to this:

Expandable tables and text fields

Hi I need to create a form in Acrobate that have expandable tables, the user should be able to add rows and I would like to have text fields that expands as the user fill in the text (not just the scrollbar at the end of the text field) I do not have the knowledge to create this myself and not...

community.adobe.com

Which also led to this:

Create form with an expanding table

I am trying to create a fax sheet for one of our customer service reps. She needs to occasionally fax over a list of names, acct #'s, etc to our collection agency to have individuals removed. Since it's done on an as needed basis, I want to use a table to create an expandable number of rows...

experienceleaguecommunities.adobe.com

I haven't followed the whole conversation yet, so I can't say if it helps here or not. Cheers!

 

[theDBguy]

Here's one more link...

Expandable Table with Totals - Stefan Cameron on Forms

Expandable Table with Totals - Stefan Cameron on Forms Building intelligent forms using Adobe LiveCycle Designer.

forms.stefcameron.com

 

[PatTahiti]

But my point was this:

1. Client creates a master PDF form
2. You loop through your reports in your Access project
3. For each report found - fill out the fields in the master PDF form and save a copy

The difference being, step 1 in my approach is performed by the client only one time. Whereas, step 3 in your approach requires the client to perform the same task as many reports and as many times you export a report to PDF.

So, in essence, you eliminate the last step in the current process, if we can create a master PDF that will work with all your reports. Imagine the work load difference of creating one master PDF compared to say 50 PDF reports that have to be converted to fillable forms - and then repeat the process again next time reports have to be generated from Access.

Anyway, I haven't had a chance to search for the "expandable" PDF form I was talking about earlier, so maybe my idea won't work anyway if that's not possible - but I could swear it's available.

The problem is not to fill out the fields, the problem is to transform all text fields into form fields.
The client can't create a master PDF form because data come from parametrized queries retrieveing different numbers of records and grouping them by family of products. So, the group titles change every time and the number of available products vary in the stock.
I assume you think I have to fill out a form, but at this point, the pdf is not a form yet, it is a pdf file containing text areas drawned by Access and these are not yet form fields.
To transform these areas into form fields, you have to "prepare the form" with Acrobat Pro. And this is what I want to automate.

By the way, thank you for spending your time for me

 

[PatTahiti]

But my point was this:

1. Client creates a master PDF form
2. You loop through your reports in your Access project
3. For each report found - fill out the fields in the master PDF form and save a copy

The difference being, step 1 in my approach is performed by the client only one time. Whereas, step 3 in your approach requires the client to perform the same task as many reports and as many times you export a report to PDF.

So, in essence, you eliminate the last step in the current process, if we can create a master PDF that will work with all your reports. Imagine the work load difference of creating one master PDF compared to say 50 PDF reports that have to be converted to fillable forms - and then repeat the process again next time reports have to be generated from Access.

Anyway, I haven't had a chance to search for the "expandable" PDF form I was talking about earlier, so maybe my idea won't work anyway if that's not possible - but I could swear it's available.

Hello,
Now I'm more experienced with PDF's thanks to you, so I can be more specific to explain my goal.
I can parse a PDF file and retreive all texts. (Content = AcroTextSelect.GetText(j) )
When generating an Access report, I can name the blank text areas that I want to be turned into fields something like "#field01#", "#field02#", etc.
The idea now is to get the coordinates of these text areas to add PDF form fields at the same location.
How can I get the coordinates of the text to be able to do this : Set fldTextField = jso.addField("Field" & j, "text", 0, coords)
The problem is to retreive the "coords" values.
AcroTextSelect.GetBoundingRect does'nt work.

Thanks!
Regards,
Patrice

 

[PatTahiti]

Solved.

Context:
My client generates 50+ Access reports containing products records grouped by family products. Each generated line/record has a blank textbox that should be filled by users to indicate the quantity of products they would order to my client.
These reports are exported to PDF files.

The problem:
My client has to transform manually each PDF file into PDF forms. Acrobat Pro propose to automatically detect areas that can be transformed into form fields. It's cool, but not if you have to do this for 50+ reports.

Solution:
To create form fields on the fly, I used this method :

• In Access, in the reports, I put a "technical" content in the textbox I want to be transformed into form fields, something like "MYFUTUREFORMFIELD"
• I export the report into a PDF file
• I scan the PDF to retreive all occurrences of the special text i.e. "MYFUTUREFORMFIELD"
• For each occurrence I create a form field at the same place

Javascript:
To do this, I had to use a Javascript file wich does the job of retreiving the special text and placing a form field at the same coordinates.
This script is submitted to and executed by the PDF file : AcroForm.Fields.ExecuteThisJavascript sJScript

Thanks to all who helped me !

Patrice