info
Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
putting data into a PDF document
118 views
Skip to first unread message
internet...@foobox.com
Jul 23, 2020, 12:17:25 PM7/23/20
to
I have a fillable pdf document that I did not create.
I been asked if I can place data into it from my database.
Looks as if it should be simple. However I do need to know or be able to name the fields (cells) that will contain the data.
Does anyone know how I can find out the fields names? Or at least give them names?
Once I have these I would need to open the pdf - any advice here would be welcome as to which application and then assign values to the named fields.
Pointers in the direction of how to do this would be welcomed.
Jim
I been asked if I can place data into it from my database.
Looks as if it should be simple. However I do need to know or be able to name the fields (cells) that will contain the data.
Does anyone know how I can find out the fields names? Or at least give them names?
Once I have these I would need to open the pdf - any advice here would be welcome as to which application and then assign values to the named fields.
Pointers in the direction of how to do this would be welcomed.
Jim
Ulrich Möller
Jul 24, 2020, 5:10:33 AM7/24/20
to
Hi Jim,
Am 23.07.2020 um 18:17 schrieb internet...@foobox.com:
> I have a fillable pdf document that I did not create.
>
> I been asked if I can place data into it from my database.
>
> Looks as if it should be simple. However I do need to know or be able to name the fields (cells) that will contain the data.
>
> Does anyone know how I can find out the fields names? Or at least give them names?
>
> Once I have these I would need to open the pdf - any advice here would be welcome as to which application and then assign values to the named fields.
>
you can use PDFtk Tools for this purpose.
See: https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
Ulrich
Am 23.07.2020 um 18:17 schrieb internet...@foobox.com:
> I have a fillable pdf document that I did not create.
>
> I been asked if I can place data into it from my database.
>
> Looks as if it should be simple. However I do need to know or be able to name the fields (cells) that will contain the data.
>
> Does anyone know how I can find out the fields names? Or at least give them names?
>
> Once I have these I would need to open the pdf - any advice here would be welcome as to which application and then assign values to the named fields.
>
See: https://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/
Ulrich
Mal Reeve
Jul 24, 2020, 9:15:12 AM7/24/20
to
This post relates to Excel, but the VBA would work in Access as well.
https://www.excelforum.com/excel-programming-vba-macros/1204659-writing-excel-data-into-pdf-form.html
It reads a pdf form to get all the field names and current values, and can also write to them as well.
I've adapted it to look loop through whatever fields it finds, rather than hard coding the field names.
Posting my slightly adjusted code below as well.
I am only beginning this journey of writing to a pdf...so can't offer much more help than this.
I'm about to explore what happens when I try to write to a field that doesn't exist.....
Good Luck,
Mal.
Sub ReadAdobeFields()
'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
'Tools, References, Select the Adobe Acrobat Library.
row_number = 10 'Adjust for where to start on Sheet.
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sWrkshtName As String
Dim sFileName As String
'On Error Resume Next
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
sFileName = Range("D5").Value
If AcrobatDocument.Open(sFileName, "") Then
AcrobatApplication.Show
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count ' Number of Fields
For Each Field In Fields
ActiveSheet.Range("D" & row_number) = Field.Name
ActiveSheet.Range("E" & row_number) = Field.Value
row_number = row_number + 1
Next Field
Else
MsgBox "FAIL", vbOKOnly
End If
'Close template file & Acrobat.
AcrobatDocument.Close True
'AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
Set ActoForm = Nothing
End Sub
Sub WriteAdobeFields()
'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
'Tools, References, Select the Adobe Acrobat Library.
row_number = 10 'Adjust for where to start on Sheet.
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sFieldName As String
Dim sFolderPath As String
Dim sFileName As String
Dim sWrkshtName As String
Dim sTemplateName As String
Dim sDATAtoWrite As String
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
sTemplateName = Range("D5").Value
sWrkshtName = ActiveSheet.Name
'ADJUST to pass name of Template from each Worksheet
If AcrobatDocument.Open(sTemplateName, "") Then
AcrobatApplication.Show
AcrobatApplication.Maximize 1
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count ' Number of Fields
For Each Field In Fields
sFieldName = ActiveSheet.Range("D" & row_number).Value
sDATAtoWrite = ActiveSheet.Range("F" & row_number).Value
'check to see if this field is one to write, skip if not
If IsEmpty(ActiveSheet.Range("G" & row_number).Value) = False Then
Fields(sFieldName).Value = sDATAtoWrite
ActiveSheet.Range("h" & row_number).Value = "Done!"
Else
ActiveSheet.Range("h" & row_number).Value = "skipped"
End If
row_number = row_number + 1
Next Field
'ORIGINAL CODE COMMENTED OUT HERE
' Fields("Adults").Value = sWrkshtName.Range("C2").Value
' Fields("Students").Value = sWrkshtName.Range("C30").Value
' Fields("Group Name").Value = sWrkshtName.Range("C17").Value
' Fields("Campers NameRow1").Value = sWrkshtName.Range("C5").Value
' Fields("Campers NameRow2").Value = sWrkshtName.Range("C6").Value
' Fields("Campers NameRow3").Value = sWrkshtName.Range("C7").Value
' Fields("lunch").Value = sWrkshtName.Range("C51").Value
Else
MsgBox "failure"
End If
'Save PDF File
'Leave open for edit/printing
'? Close original template file
sFolderPath = "C:\Users\Administrator\Desktop\ExcelFilesForV360"
sFileName = sFolderPath & "\" & Fields("GroupName").Value & "-" & Format(Date, "dd-mm-yy") & ".pdf"
Set AcroApp = CreateObject("AcroExch.App")
Set avdoc = AcroApp.GetActiveDoc
If Not (avdoc Is Nothing) Then
Set PdDoc = avdoc.GetPDDoc
PdDoc.Save PDSaveFull, sFileName
End If
'Close template file here.
'AcrobatDocument.Close 1
'AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
Set ActoForm = Nothing
End Sub
https://www.excelforum.com/excel-programming-vba-macros/1204659-writing-excel-data-into-pdf-form.html
It reads a pdf form to get all the field names and current values, and can also write to them as well.
I've adapted it to look loop through whatever fields it finds, rather than hard coding the field names.
Posting my slightly adjusted code below as well.
I am only beginning this journey of writing to a pdf...so can't offer much more help than this.
I'm about to explore what happens when I try to write to a field that doesn't exist.....
Good Luck,
Mal.
Sub ReadAdobeFields()
'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
'Tools, References, Select the Adobe Acrobat Library.
row_number = 10 'Adjust for where to start on Sheet.
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sWrkshtName As String
Dim sFileName As String
'On Error Resume Next
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
sFileName = Range("D5").Value
If AcrobatDocument.Open(sFileName, "") Then
AcrobatApplication.Show
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count ' Number of Fields
For Each Field In Fields
ActiveSheet.Range("D" & row_number) = Field.Name
ActiveSheet.Range("E" & row_number) = Field.Value
row_number = row_number + 1
Next Field
Else
MsgBox "FAIL", vbOKOnly
End If
'Close template file & Acrobat.
AcrobatDocument.Close True
'AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
Set ActoForm = Nothing
End Sub
Sub WriteAdobeFields()
'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
'Tools, References, Select the Adobe Acrobat Library.
row_number = 10 'Adjust for where to start on Sheet.
Dim AcrobatApplication As Acrobat.CAcroApp
Dim AcrobatDocument As Acrobat.CAcroAVDoc
Dim fcount As Long
Dim sFieldName As String
Dim sFolderPath As String
Dim sFileName As String
Dim sWrkshtName As String
Dim sTemplateName As String
Dim sDATAtoWrite As String
Set AcrobatApplication = CreateObject("AcroExch.App")
Set AcrobatDocument = CreateObject("AcroExch.AVDoc")
sTemplateName = Range("D5").Value
sWrkshtName = ActiveSheet.Name
'ADJUST to pass name of Template from each Worksheet
If AcrobatDocument.Open(sTemplateName, "") Then
AcrobatApplication.Show
AcrobatApplication.Maximize 1
Set AcroForm = CreateObject("AFormAut.App")
Set Fields = AcroForm.Fields
fcount = Fields.Count ' Number of Fields
For Each Field In Fields
sFieldName = ActiveSheet.Range("D" & row_number).Value
sDATAtoWrite = ActiveSheet.Range("F" & row_number).Value
'check to see if this field is one to write, skip if not
If IsEmpty(ActiveSheet.Range("G" & row_number).Value) = False Then
Fields(sFieldName).Value = sDATAtoWrite
ActiveSheet.Range("h" & row_number).Value = "Done!"
Else
ActiveSheet.Range("h" & row_number).Value = "skipped"
End If
row_number = row_number + 1
Next Field
'ORIGINAL CODE COMMENTED OUT HERE
' Fields("Adults").Value = sWrkshtName.Range("C2").Value
' Fields("Students").Value = sWrkshtName.Range("C30").Value
' Fields("Group Name").Value = sWrkshtName.Range("C17").Value
' Fields("Campers NameRow1").Value = sWrkshtName.Range("C5").Value
' Fields("Campers NameRow2").Value = sWrkshtName.Range("C6").Value
' Fields("Campers NameRow3").Value = sWrkshtName.Range("C7").Value
' Fields("lunch").Value = sWrkshtName.Range("C51").Value
Else
MsgBox "failure"
End If
'Save PDF File
'Leave open for edit/printing
'? Close original template file
sFolderPath = "C:\Users\Administrator\Desktop\ExcelFilesForV360"
sFileName = sFolderPath & "\" & Fields("GroupName").Value & "-" & Format(Date, "dd-mm-yy") & ".pdf"
Set AcroApp = CreateObject("AcroExch.App")
Set avdoc = AcroApp.GetActiveDoc
If Not (avdoc Is Nothing) Then
Set PdDoc = avdoc.GetPDDoc
PdDoc.Save PDSaveFull, sFileName
End If
'Close template file here.
'AcrobatDocument.Close 1
'AcrobatApplication.Exit
Set AcrobatApplication = Nothing
Set AcrobatDocument = Nothing
Set Field = Nothing
Set Fields = Nothing
Set ActoForm = Nothing
End Sub
Mal Reeve
Jul 24, 2020, 9:22:01 AM7/24/20
to
Forgot to highlight/mention...
'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
'Tools, References, Select the Adobe Acrobat Library.
'Tools, References, Select the Adobe Acrobat Library.
Ulrich Möller
Jul 24, 2020, 10:24:26 AM7/24/20
to
Hi Mal,
Am 24.07.2020 um 15:21 schrieb Mal Reeve:
> Forgot to highlight/mention...
> 'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
> 'Tools, References, Select the Adobe Acrobat Library.
and you also forgot to mention that an Adobe Acrobat must be installed
and license is required.
Ulrich
Am 24.07.2020 um 15:21 schrieb Mal Reeve:
> Forgot to highlight/mention...
> 'Note: Remember to add your Adobe Acrobat Library in the VB Environment.
> 'Tools, References, Select the Adobe Acrobat Library.
and license is required.
Ulrich
internet...@foobox.com
Jul 24, 2020, 12:25:26 PM7/24/20
to
Thank you Mal and Ulrich for your comments.
They are helpful and informative.
It would appear that pdftk is a command line system. I was hoping for an API that could be used in the code. I have yet to download it to try it.
I like the Adobe approach and is the way I had in mind. However I am aware that a license would be needed.
I have seen another way of doing it by using SendKeys. Not a method I would normally adopt but is worth a try.
Jim
They are helpful and informative.
It would appear that pdftk is a command line system. I was hoping for an API that could be used in the code. I have yet to download it to try it.
I like the Adobe approach and is the way I had in mind. However I am aware that a license would be needed.
I have seen another way of doing it by using SendKeys. Not a method I would normally adopt but is worth a try.
Jim
Ron Paii
Jul 24, 2020, 5:25:04 PM7/24/20
to
Trap error 429 "ActiveX component can't create object" when creating the application object.
Set AcrobatApplication = CreateObject("AcroExch.App")
You will only need Adobe Acrobat on computers importing PDF document data.
Mal Reeve
Jul 29, 2020, 1:57:02 AM7/29/20
to
I have just discovered this approach which I believe would work well.
It does depend on knowing the field names.
The sample file it includes has a routine that will get those field name - IF you have the full version of ACROBAT installed.
While I found a few postings about getting those names with READER DC - none of them worked for me.
Perhaps dig up an older version of the free Reader software - which would apparently let you export the form data and take a look. (untested)
Anyway....this page:
http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html
Shows how to use create an "fdf" file (Form data only).
It's just a specially formatted text file.
Once created, you just open it....it grabs the default program (Acrobat or Reader DC) and drops the data into the fields.
I've done some basic testing with my situation and it's all working well with hard coded field names and data. Now on to use it in a looping function.
Good luck!
Mal.
It does depend on knowing the field names.
The sample file it includes has a routine that will get those field name - IF you have the full version of ACROBAT installed.
While I found a few postings about getting those names with READER DC - none of them worked for me.
Perhaps dig up an older version of the free Reader software - which would apparently let you export the form data and take a look. (untested)
Anyway....this page:
http://www.excelhero.com/blog/2010/04/excel-acrobat-pdf-form-filler.html
Shows how to use create an "fdf" file (Form data only).
It's just a specially formatted text file.
Once created, you just open it....it grabs the default program (Acrobat or Reader DC) and drops the data into the fields.
I've done some basic testing with my situation and it's all working well with hard coded field names and data. Now on to use it in a looping function.
Good luck!
Mal.
Mal Reeve
Jul 29, 2020, 2:00:31 AM7/29/20
to
Ron,
I tried to get this to work, but continually got the 429 error (and trapped it).
But couldn't get the code to write to the form fields (in READER DC).
While I Found another solution (fdf file). I would have like to be able to also do things like SAVE AS through the VBA code.
Probably something with my changing it to late binding - which I really don't "get".
I changed all the DIM statements to be "as Object". but not sure what else in the posted code would need to be changed to accomplish the Late Binding.
I tried to get this to work, but continually got the 429 error (and trapped it).
But couldn't get the code to write to the form fields (in READER DC).
While I Found another solution (fdf file). I would have like to be able to also do things like SAVE AS through the VBA code.
Probably something with my changing it to late binding - which I really don't "get".
I changed all the DIM statements to be "as Object". but not sure what else in the posted code would need to be changed to accomplish the Late Binding.
internet...@foobox.com
Jul 29, 2020, 11:54:14 AM7/29/20
to
It is interesting to see that someone else is trying to do this and finding it difficult. My client and I only have Acrobat Reader and not the Pro DC version. The latter would cost £182 pa and I am not sure whether that limits the use to a single machine. The client may need to take out a number of licences.
I found an alternative approach although It would not be my preferred way of doing it:
Dim detailsRec As DAO.Recordset
Set detailsRec = CurrentDb.OpenRecordset("SELECT WAVDemo.*, Customers.AccountName, Customers.Contact, CompactAddressOnLines(Customers.Address1, Customers.Address2, Customers.Address3, Customers.Town, Customers.County) As theAddress, Customers.PostCode, Customers.TelephoneNumber FROM WAVDemo LEFT JOIN Customers ON WAVDemo.CustomerID = Customers.CustomerID")
FollowHyperLink thePdfFile
Pause 3
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("WAVConverter"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("AccountName"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("Demonstrator"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("Contact"), ""), True
Pause 1
SendKeys "{TAB}", True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(DLookup("emailAddress", "Users", "UserID = " & getCurrentUserID), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("theAddress"), "")
Pause 1
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("PostCode"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys "{TAB}", True
Pause 1
SendKeys Nz(detailsRec("TelephoneNumber"), ""), True
Pause 1
SendKeys "^s", True
Pause is my function to pause for the specified number of seconds. Seems to be necessary to slow the system down!!
What surprised me that before I installed Acrobat the pdf file opened in Microsoft Edge. Strangely when I had Acrobat and opened the file in that, the tab order of the fields was different!!! How could that be? I would have expected the order to specified within the pdf itself.
Without the full version of Acrobat I do not know the names of the fillable fields.
Our current use is limited to this and will suffice for now. I would prefer an API where I could access the objects directly
Jim
I found an alternative approach although It would not be my preferred way of doing it:
Dim detailsRec As DAO.Recordset
Set detailsRec = CurrentDb.OpenRecordset("SELECT WAVDemo.*, Customers.AccountName, Customers.Contact, CompactAddressOnLines(Customers.Address1, Customers.Address2, Customers.Address3, Customers.Town, Customers.County) As theAddress, Customers.PostCode, Customers.TelephoneNumber FROM WAVDemo LEFT JOIN Customers ON WAVDemo.CustomerID = Customers.CustomerID")
FollowHyperLink thePdfFile
Pause 3
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("WAVConverter"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("AccountName"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("Demonstrator"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("Contact"), ""), True
Pause 1
SendKeys "{TAB}", True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(DLookup("emailAddress", "Users", "UserID = " & getCurrentUserID), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("theAddress"), "")
Pause 1
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys Nz(detailsRec("PostCode"), ""), True
Pause 1
SendKeys "{TAB}", True
SendKeys "{TAB}", True
Pause 1
SendKeys Nz(detailsRec("TelephoneNumber"), ""), True
Pause 1
SendKeys "^s", True
Pause is my function to pause for the specified number of seconds. Seems to be necessary to slow the system down!!
What surprised me that before I installed Acrobat the pdf file opened in Microsoft Edge. Strangely when I had Acrobat and opened the file in that, the tab order of the fields was different!!! How could that be? I would have expected the order to specified within the pdf itself.
Without the full version of Acrobat I do not know the names of the fillable fields.
Our current use is limited to this and will suffice for now. I would prefer an API where I could access the objects directly
Jim
0 new messages