I've asked this a while ago on Stack Exchange. Haven't gotten much for replies since, so I was wondering if anyone here could pass on any insight as to how this might be accomplished!
Don't worry about replying to my question on Stack Exchange. If I get an answer, I'll post the answer on Stack and remove this thread to save duplication.
Hi,
I've definitely seen vba code knocking around in the past for Excel that uses PDFCreator (open source) to generate PDF docs.
Perhaps it could be adapted to do what you're after.
However, the PDF creation isn't the unknown I'm dealing with in this scenario. It's figuring out, using Access VBA and potentially javascript, how to...
Export a PDF
Change the PDF (within VBA) to a PDF Form (will need to know proper Adobe Acrobat object references to use)
Add fillable and calcuated fields (will need a VBA function to determine where to place certain fillable fields)
Add blank for inserting logo? (if this is even possible, I know a workaround if not)
Save changes and close PDF form
Notify end user when this entire process is complete
The first and last steps are no big deal either... it's the meat of this complex sandwich that I'm stumped with.
Blimey.
OK, you're going to need a decent api to connect to.
Because you're generating forms you'll need something quite well documented.
A quick google search for me turned up this: http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/iac_api_reference.pdf
it's a bit old but on page 183 it starts to describe calls for form generation.
It might be worth wading through this stuff to see if it takes you anywhere.
I think that unless someone has specifically done this before in Access you're unlikely to get a complete answer so you may have some leg work to do.
I do know there's all sorts of interfaces out there (probably mainly based on ghostscript in some way) that might be worth looking at.
I'm sorry I haven't got any real answers for you but I would be very interested in how you get on so I hope you post any information you find!
My thoughts exactly when this request was projected to me!
Yeah, and that's the thing, from the searching I've done I haven't found anything well-documented for using Adobe api's to do what I'm attempting to accomplish. Merely a "you can do this in Adobe!" with two thumbs up
As for the complete solution, I knew this would be a rarity to find and I'm prepared to do the necessary leg work for this to work.
Definitely will be posting answers/progress on how to do this! I could see this being quite useful once complete.
I might consider just doing that eventually, spikepl. Especially since we have Acrobat X Pro to deploy here.
However, if there is a possible way to automate this within VBA then I know this option would be preferred.
Update:
So far I'm able to add a fillable field to the exported PDF report from Access using the Acrobat library references, save the PDF then open it in Adobe:
Code:
'Create Acrobat objects
Dim AcrobatApp As Acrobat.AcroApp
Dim ProductsPDF As Acrobat.AcroPDDoc
Dim jsObj As Object
Dim nPages As Integer
Dim rect(0 To 3) As Integer
Set AcrobatApp = CreateObject("AcroExch.App")
Set ProductsPDF = CreateObject("AcroExch.PDDoc")
'Close existing Products PDF file if open
Dim PDFClose As Acrobat.AcroAVDoc
Set PDFClose = CreateObject("AcroExch.AVDoc")
If PDFClose.Open("C:\ProductsPDFForm.pdf", "") = True Then
PDFClose.Close (1)
End If
Set PDFClose = Nothing
'Export and open Products PDF
DoCmd.OutputTo acOutputReport, "Products Rpt", acFormatPDF, "C:\ProductsPDFForm.pdf", False
ProductsPDF.Open ("C:\ProductsPDFForm.pdf")
'Add field to PDF
nPages = ProductsPDF.GetNumPages()
Set jsObj = ProductsPDF.GetJSObject()
rect(0) = 288
rect(1) = 475
rect(2) = 576
rect(3) = 500
Dim TestField As Object
Set TestField = jsObj.addfield("thisthing", "text", 0, rect)
TestField.Value = "Enter here..."
TestField.textsize = 12
TestField.textcolor = jsObj.Color.blue
TestField.FillColor = jsObj.Color.ltgray
'Try to save PDF File
If ProductsPDF.Save(PDSaveFull, "C:\ProductsPDFForm.pdf") = False Then
MsgBox "Something went wrong... check the code!"
Else
'Let user know it saved and open PDF upon clicking OK
Dim mSQL As String
Dim shell As Object
mSQL = "PDF Form created! Here's the location:" & vbCr & vbCr
mSQL = mSQL & "C:\ProductsPDFForm.pdf" & vbCr & vbCr
mSQL = mSQL & "PDF will now open."
MsgBox mSQL
Set shell = CreateObject("WScript.Shell")
shell.Run Chr(34) & "C:\ProductsPDFForm.pdf" & Chr(34), 1, False
End If
'Kill remaining objects
ProductsPDF.Close
Set AcrobatApp = Nothing
Set ProductsPDF = Nothing
One objective down, many more to go! Here's a site I referenced for those interested in seeing how I got this far.
Now, the next task will be to grab pricing values used in the report and add fillable and/or calculated fields to the PDF where these pricing values are located one each page...
Once this is done, the next objective will be able to give the recipient of this fillable PDF form the ability to add their own dealer page header (.png) where ours currently resides (not sure if you can even do this with a PDF form or not...).
Slowly making process bit by bit, but now I've hit a wall...
I need to grab pricing values used on the report and find them on the PDF. However, when I go to find "$46,800" on the PDF I'm only getting "46,800". What I'm doing seems to ONLY pull the text (and any special characters in between) from the PDF:
Code:
'grab base price value from Test Quote recordset
Dim db As Database
Dim rs As Recordset
Dim rsSQL As String
Set db = CurrentDb
rsSQL = "select FormatCurrency([BasePrice],0) AS BP from [Test Quote] group by FormatCurrency([BasePrice],0)"
Set rs = db.OpenRecordset(rsSQL)
MsgBox rs(0).Value
'find base price in Test Quote PDF (testing)
'coding reference: http://www.myengineeringworld.net/2014/05/pdf-search-through-vba.html
Dim FindTxtinPDF As Acrobat.AcroAVDoc
Dim FindTxtinPDFDoc As Acrobat.AcroPDDoc
Dim jsFind As Object
Dim pgs As Long
Dim wrds As Long
Dim Word As Variant
Dim Result As Integer
Dim FindThis As String
Set FindTxtinPDF = CreateObject("AcroExch.AVDoc")
FindThis = rs(0).Value
If FindTxtinPDF.Open(Export, "") = True Then
'if PDF is open, look for text using JavaScript Object
FindTxtinPDF.BringToFront
Set FindTxtinPDFDoc = FindTxtinPDF.GetPDDoc
Set jsFind = FindTxtinPDFDoc.GetJSObject
If Not jsFind Is Nothing Then
For pgs = 0 To jsFind.numpages - 1
For wrds = 0 To jsFind.getpagenumwords(pgs) - 1
Word = jsFind.getpagenthword(pgs, wrds)
If VarType(Word) = vbString Then
Result = StrComp(Word, FindThis, vbTextCompare)
If Result = 0 Then
Call jsFind.selectpagenthword(pgs, wrds)
MsgBox "Found it!"
End If
End If
Next wrds
Next pgs
'text was not found, close the object and inform user
FindTxtinPDF.Close True
Set jsFind = Nothing
Set FindTxtinPDF = Nothing
Set FindTxtinPDFDoc = Nothing
End If
Else
'PDF doesn't exist, inform user
Set FindTxtinPDF = Nothing
Set FindTxtinPDFDoc = Nothing
MsgBox "PDF doesn't exist!", vbCritical, "File Error"
End If
I've been reading the Acrobat reference books as well as the rest of the internet, but I don't see a function and/or way to pull special characters with the word instead of trimming them off...
EDIT/UPDATE:
Instead of looking for the price value, I've decided to look for strings of text ("BasePriceGoesHere", "OptionPriceGoeshere", etc.) and this won't cause any issues going forward. Now, I'm currently working on finding the selected text's bounding rectangle and putting a fillable/calculated field in its place.
This time, it's because I'm currently having issues with grabbing the selected text's bounding rectangle (to insert a fillable/calculated field on top of the selected text).
I've been reading up on the Acrobat reference books still (mainly this one), plus other places on the web. However, I still can't bridge the gap between the selected text and passing the location of the selected text to the ArcoExch.PDTextSelect.GetBoundingRect object method.
Currently, I'm selecting text with the following segment of code (see previous post for full selection):
Code:
'if PDF is open, look for text using JavaScript Object
FindTxtinPDF.BringToFront
Set FindTxtinPDFDoc = FindTxtinPDF.GetPDDoc
Set jsFind = FindTxtinPDFDoc.GetJSObject
If Not jsFind Is Nothing Then
For pgs = 0 To jsFind.numpages - 1
For wrds = 0 To jsFind.getpagenumwords(pgs) - 1
Word = jsFind.getpagenthword(pgs, wrds)
If VarType(Word) = vbString Then
Result = StrComp(Word, FindThis, vbTextCompare)
If Result = 0 Then
Call jsFind.selectpagenthword(pgs, wrds)
MsgBox "Found it!"
End If
End If
Next wrds
Next pgs
'text was not found, close the object and inform user
FindTxtinPDF.Close True
Set jsFind = Nothing
Set FindTxtinPDF = Nothing
Set FindTxtinPDFDoc = Nothing
End If
This line highlights the text I'm looking for within Adobe with no issues:
Code:
Call jsFind.selectpagenthword(pgs, wrds)
I merely want to pass the highlighted to the GetBoundingRect Acrobat method so I can get the proper rectangle values for creating a field to place on top of it. I've also followed this as a generic coding roadmap to the GetBoundingRect method, but to no further avail:
Whenever I do attempt, I get one of the following VBA errors:
Run-time error '-91' - Object variable or With block variable not set
Error 424 - Object Required
Error 429 - ActiveX component can't create object
I offer my apologies as I've walked away from this and went to another problem before coming back to this project, so I don't recall all the different object and createobject attempts I've tried.
If it also requires rewriting what I currently have for selecting text on a PDF using Acrobat object methods, I'm all ears. What I have so far is all test code to help me slowly reach my end goal .
Edit:
If anyone would like to see the project as a whole, I will upload a stripped-down front-end for you to play with. All you have to do is ask