Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average. Display Modes
Old 09-29-2015, 11:52 AM   #1
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Question Export Access 2007 Report to Auto-Generted PDF Form

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.

SkyCraw is offline   Reply With Quote
Old 09-29-2015, 02:51 PM   #2
ozinm
Human Coffee Siphon
 
Join Date: Jul 2003
Location: UK
Posts: 121
Thanks: 1
Thanked 9 Times in 7 Posts
ozinm is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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.
ozinm is offline   Reply With Quote
Old 09-29-2015, 02:53 PM   #3
ozinm
Human Coffee Siphon
 
Join Date: Jul 2003
Location: UK
Posts: 121
Thanks: 1
Thanked 9 Times in 7 Posts
ozinm is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

Oh hang on...
Here you go:
http://www.access-programmers.co.uk/...d.php?t=201939

ozinm is offline   Reply With Quote
Old 09-30-2015, 03:13 AM   #4
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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.
SkyCraw is offline   Reply With Quote
Old 09-30-2015, 02:17 PM   #5
ozinm
Human Coffee Siphon
 
Join Date: Jul 2003
Location: UK
Posts: 121
Thanks: 1
Thanked 9 Times in 7 Posts
ozinm is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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/Ado..._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.
ozinm is offline   Reply With Quote
Old 10-01-2015, 03:08 AM   #6
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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 is offline   Reply With Quote
Old 10-01-2015, 11:42 PM   #7
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Export Access 2007 Report to Auto-Generted PDF Form

One option: http://www.vbforums.com/showthread.p...an-Access-form

__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
Old 10-02-2015, 03:30 AM   #8
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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 is offline   Reply With Quote
Old 10-02-2015, 10:14 AM   #9
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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 by SkyCraw; 10-05-2015 at 04:59 AM. Reason: Found a workaround for my question.
SkyCraw is offline   Reply With Quote
Old 10-05-2015, 08:25 AM   #10
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

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 by SkyCraw; 10-08-2015 at 06:55 AM. Reason: additional information
SkyCraw is offline   Reply With Quote
Old 10-16-2015, 04:08 AM   #11
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

Anyone have any say on what I could do about my previous post?

Last edited by SkyCraw; 11-10-2015 at 04:49 AM. Reason: bump thread back to top
SkyCraw is offline   Reply With Quote
Old 11-10-2015, 04:49 AM   #12
SkyCraw
Newly Registered User
 
Join Date: Oct 2013
Location: Canada
Posts: 98
Thanks: 18
Thanked 2 Times in 2 Posts
SkyCraw is on a distinguished road
Re: Export Access 2007 Report to Auto-Generted PDF Form

Or have I "broke the internet" (so to speak) with my inquiry :P

SkyCraw is offline   Reply With Quote
Reply

Tags
javascript , pdf , vba access 2007

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Export report to Excel in Access 2007 rej2008 Macros 5 03-28-2011 07:05 PM
Export to Excel Access 2003 report in Access 2007 digger General 1 03-01-2010 05:56 AM
Access 2007 Report Export to Excel shadow9449 Reports 6 08-26-2009 01:05 PM
MS Access 2007 Report - Export to Excel not there smartcard Reports 4 12-03-2007 03:34 PM
Export Access 2007 Report into a PDF KAT1162 Reports 1 08-06-2007 06:39 PM




All times are GMT -8. The time now is 07:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World