Is it possible to export data into fillable pdf file without having to export data to Excel first?

**SGrohola** · 03-29-2017, 01:54 PM

I really appreciate all the help that I have gotten on this board, and I'm getting towards the end of creating this database.

I have several fillable pdf forms. On the simpler forms, I created reports to capture the data, and then save them as .pdf file. But, I have some more that have quite a lot of verbiage on them, and I would like to just be able to export the data to the pdf file and have the fields filled in with the data. I have already made sure that the field names in the pdf form are the same as the field names in my Access database.

What I would like to be able to do, is create a macro, that will automate the process. It will export the data and save as a .txt file, then open the pdf form and merge the data into the form. It sounds doable, but I don't even know where to start with it.

Can anyone possibly help me with this??

Thanks!

**andy49** · 03-29-2017, 02:55 PM

http://khkonsulting.com/2010/09/read...elds-with-vba/

This page shows how for export from a form into a PDF. Not sure if it's any use.

Sent from my iPhone using Tapatalk

**Micron** · 03-29-2017, 07:42 PM

Not sure a similar issue was ever solved, but this has been asked here before. Maybe this one can help too
https://www.accessforums.net/showthread.php?t=63902

**NTC** · 03-31-2017, 08:13 AM

Actually I think this is a question for an Adobe forum. The link-ability of fillable PDF would be a property of that PDF. With VBA one can export Access data and save as a .txt file, then open the pdf form....but beyond that in terms of that PDF importing data - one would need to check with PDF development features....

**Micron** · 03-31-2017, 01:16 PM

Originally Posted by NTC

Actually I think this is a question for an Adobe forum. The link-ability of fillable PDF would be a property of that PDF. With VBA one can export Access data and save as a .txt file, then open the pdf form....but beyond that in terms of that PDF importing data - one would need to check with PDF development features....

Don't agree at this point. The link pointed to a thread, which had a link to another thread. That second thread solved an issue of getting pdf field data into Access and I'm pretty sure the OP solved the problem. It also contained a link to an Adobe paper on accessing pdf data with vba as I recall. So if one can get pdf data into Access, it stands to reason that it can be pushed into a pdf form as well, but I don't think the way to go about it is to export data to a text file first as the OP stated. Speaking of which, when there's no response from the OP for a couple of days but there are several replies it could mean this might be posted elsewhere.

**NTC** · 04-01-2017, 07:29 AM

My primary point for the newer developers at this forum is that creating a pdf is an embedded feature of Access. But that differs from the role of Access holding data that ends up in a predefined fillable PDF template. That type of PDF fillable template can link to potentially a wide variety of data sources of which Access is just one. And therefore one presumes in an Adobe forum there are lots of Q&A on how to accomplish that linking.
Of course any question can be answered at any forum and all relevant input helps.

**SGrohola** · 04-03-2017, 06:43 AM

My apologies for not posting recently. I was working hard on Thursday and Friday trying to get the VBA coding to work, and with barely any good results. I, unfortunately, am not a programmer, and have never claimed to be. I have a hard time with some of the coding, especially if it doesn't work, on how to get it to work. I was able to get one of the codes to populate in my pdf form, but only if I put in the "text". I want it to be able to read and pull the text from fields associated to the owner/operator. I created a report with those fields, and associated it with the ID field. Added a button to the form, but with no such luck to pull the data already there. Can anyone help me with this code? The code that I have is one from above, and it worked by putting "13" into my .pdf document. BUT, the pdf form had to be opened. I want the code to open the form, look for the data in the fields that I put on there, and then populate the fields in the pdf form. I looked at so many different codes for this last week, but still hitting my head against a brick wall.

Code:

Private Sub Command89_Click()
    Dim AcroApp As Acrobat.CAcroApp
    Dim theForm As Acrobat.CAcroPDDoc
    Dim jso As Object
    Dim FirstName, LastName As String
    
    Set AcroApp = CreateObject("AcroExch.App")
    Set theForm = CreateObject("AcroExch.PDDoc")
    theForm.Open ("H:\Projects\OO Contract Merger\Forms\TestForm.pdf")
    Set jso = theForm.GetJSObject
    
    ' get the information from the form fields
    FirstName = jso.GetField("FirstName").Value
    LastName = jso.GetField("LastName").Value
    
    MsgBox "Values read from PDF: " & FirstName & " " & LastName
    
    ' set a text field
    Dim field2 As Object
    Set field2 = jso.GetField("LastName")
    
    field2.Value = "13" 'assign the number 13 to the fields value
    
    ' get the information from the form fields FirstName and LastName
    FirstName = jso.GetField("FirstName").Value
    LastName = jso.GetField("LastName").Value
    
    MsgBox "Values read from PDF: " & FirstName & " " & LastName
    
    theForm.Close
    
    AcroApp.Exit
    Set AcroApp = Nothing
    Set theForm = Nothing
    
    MsgBox "Done"
        
End Sub

**Micron** · 04-03-2017, 12:10 PM

Dim FirstName, LastName As String
this is not how to declare multiple variables on one line if you want what I think you do. The first is a variant because you have not declared it to be otherwise. I think you mean
Dim FirstName As String, LastName As String

I'm confused. At first you said

I would like to just be able to export the data to the pdf file and have the fields filled in with the data.

but the interchaning of terms like form, field and report plus the fact that your code mostly gets data from (not puts data into) a pdf document leaves me wondering which is which. Please clarify, and let's go with
- form = Access form
- control = Access form control; not 'field'
- pdf = the pdf document, which I think we both agree has to be a pdf form that can be filled out (e.g. is not locked)
- report = Access report

OR if you're as close as I think you are and we're talking about Access form controls, then
field2.Value = "13" should be more like
field2.Value = Me.txtNameOfYourTextbox, or you can assign a bunch of variables, one for each form control value that you need. There are other ways I might do this as well, such as using an array but it depends on the number of form controls involved. The former would be simpler for you. Note, I don't know if the pdf would require the value from Me.txtNameOfYourTextbox to be wrapped in quotes, and if so, whether they have to be double or if single would be OK. By that I mean the pdf might require
field2.Value = "'" & Me.txtNameOfYourTextbox & "'"

**SGrohola** · 04-04-2017, 06:21 AM

I knew it was confusing when I wrote it out yesterday. The code that I put on here, is code that I found, and I got it to work, but only the way that it was written. I don't have enough programming experience to change it to what I need. My database houses owner/operators. Each time an owner/operator is added, I want to be able to click a button on the form and have it open a subform, which will open all the data fields that are required for my .pdf file. Then, I want to click another button, and have the code, open the pdf file, and then extract the data from the fields and populate it into the pdf file.

I will make the changes that you noted below and see if that works. I was so frustrated on Friday... I found about 5 different codes, but this one was the only one that I could actually get to work, even if it's not working the way that I need it to.

Originally Posted by Micron

Dim FirstName, LastName As String
this is not how to declare multiple variables on one line if you want what I think you do. The first is a variant because you have not declared it to be otherwise. I think you mean
Dim FirstName As String, LastName As String

I'm confused. At first you said

but the interchaning of terms like form, field and report plus the fact that your code mostly gets data from (not puts data into) a pdf document leaves me wondering which is which. Please clarify, and let's go with
- form = Access form
- control = Access form control; not 'field'
- pdf = the pdf document, which I think we both agree has to be a pdf form that can be filled out (e.g. is not locked)
- report = Access report

OR if you're as close as I think you are and we're talking about Access form controls, then
field2.Value = "13" should be more like
field2.Value = Me.txtNameOfYourTextbox, or you can assign a bunch of variables, one for each form control value that you need. There are other ways I might do this as well, such as using an array but it depends on the number of form controls involved. The former would be simpler for you. Note, I don't know if the pdf would require the value from Me.txtNameOfYourTextbox to be wrapped in quotes, and if so, whether they have to be double or if single would be OK. By that I mean the pdf might require
field2.Value = "'" & Me.txtNameOfYourTextbox & "'"

**SGrohola** · 04-04-2017, 10:34 AM

Now, I'm getting a run-time error 91 object variable or with block variable not set.

I attempted to change the code to be able to read the field that is assigned to that record, but now I'm getting error.

Code:

Private Sub Command41_Click()


    Dim AcroApp As Acrobat.CAcroApp
    Dim theForm As Acrobat.CAcroPDDoc
    Dim jso As Object
    Dim TrailerUnitNo As String, TruckNo As String
    
    Set AcroApp = CreateObject("AcroExch.App")
    Set theForm = CreateObject("AcroExch.PDDoc")
    theForm.Open ("H:\Projects\OO Contract Merger\Forms\TestForm.pdf")
    Set jso = theForm.GetJSObject
    
    ' get the information from the form fields
    TrailerUnitNo = jso.GetField("TrailerUnitNo").Value
    TruckNo = jso.GetField("TruckNo").Value
    
    MsgBox "Values read from PDF: " & TrailerUnitNo & " " & TruckNo
    
    ' set a text field
    Dim field2 As Object
    Set field2 = jso.GetField("TruckNo")
    
    field2.Value = Me.TruckNo 'want it to read field "TruckNo" that is assigned to record
    
    ' get the information from the form fields FirstName and LastName
    TrailerUnitNo = jso.GetField("TrailerUnitNo").Value
    TruckNo = jso.GetField("TruckNo").Value
    
    MsgBox "Values read from PDF: " & TrailerUnitNo & " " & TruckNo
    
    theForm.Close
    
    AcroApp.Exit
    Set AcroApp = Nothing
    Set theForm = Nothing
    
    MsgBox "Done"
        
End Sub

**Micron** · 04-04-2017, 10:56 AM

Do you have Option Explicit at the top of every module (right under Option Compare Database)? If not, this (and worse) is what you can expect. This is a system level option that should be turned on in the vb editor Tools > Options > Require Variable Declaration. I suspect it is this line
field2.Value = Me.TruckNo 'want it to read field "TruckNo" that is assigned to record
but if you read my signature, you'll see that we shouldn't have to guess at which line the error occurs.

For those who are easily confused due to copious quantities of alcoholic beverages, being too early in the day, or having more gray hair than gray matter, let's ensure you want to reference a form control value since it might have a different name than its underlying field. In fact, IMHO, they should not have the same name. E.G. if the control is named txtTruckNo, you cannot use Me and refer to the underlying field name. If they are named the same, sometimes it causes strange results, but not the error message you're getting.

Again, since it's not early in the day, and it's too early for drinking in my parts, the reason why you need to specify where the error is must be the last reason given.

**SGrohola** · 04-04-2017, 11:04 AM

The error is at

Code:

TrailerUnitNo=jso.GetField("TrailerUnitNo").Value

Sorry that i didn't include that earlier.... I'm ready to throw my laptop out the window.

**Micron** · 04-04-2017, 01:41 PM

Can't see anything wrong with it. Maybe the pdf form has no field by that exact name spelling.
I guess since you didn't comment on the Access report output as a pdf, it's of no interest.
All I can suggest now is to zip your db and pdf form and see if I or anyone else can make it work. If you do, at least confirm that what you want is to copy over db data into the pdf form fields, since as I mentioned, part of your code seems to be for getting data from the pdf.

**SGrohola** · 04-05-2017, 07:11 AM

Owner Operators2.zip TestForm.pdf

I stripped down the database and took out a lot of data, but kept the information that was needed. I also attached the test form that I've been using for this. Any help would be appreciated.

Originally Posted by Micron

Can't see anything wrong with it. Maybe the pdf form has no field by that exact name spelling.
I guess since you didn't comment on the Access report output as a pdf, it's of no interest.
All I can suggest now is to zip your db and pdf form and see if I or anyone else can make it work. If you do, at least confirm that what you want is to copy over db data into the pdf form fields, since as I mentioned, part of your code seems to be for getting data from the pdf.