Export Access 2007 Report to Auto-Generted PDF Form (1 Viewer)

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
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.
 

ozinm

Human Coffee Siphon
Local time
Today, 12:58
Joined
Jul 10, 2003
Messages
121
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.
 

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
Thanks for the reply, ozinm!

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.
 

ozinm

Human Coffee Siphon
Local time
Today, 12:58
Joined
Jul 10, 2003
Messages
121
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!

Good luck on your search.

M.
 

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
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 :p

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.

Once again, thanks for all your help ozinm! :)
 

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
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...).

Then that's it (for now)! :)
 

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
So far, so good!

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.
 
Last edited:

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
Back again...

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:
Diagram.jpg

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 :)
 
Last edited:

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
Anyone have any say on what I could do about my previous post?
 
Last edited:

SkyCraw

Registered User.
Local time
Today, 09:58
Joined
Oct 9, 2013
Messages
100
Or have I "broke the internet" (so to speak) with my inquiry :p
 

Users who are viewing this thread

Top Bottom