Need example of VBA code that will take data from stored procedure fields and place it in PDF fields.

Posted on 2011-05-04
Last Modified: 2012-05-11
Need an example of VBA code that will take data from stored procedure fields and place it in PDF fields.
Question by:answertime
    LVL 1

    Expert Comment

    Hi There,

    My colleague had created an export feature from an Access database which exports data into xml format then using the Form->Import function on the PDF to import. This I assume is easier than using VBA coding perhaps?
    Search for 'import data into PDF form' on the interwebs and you'll find something which will guide you.
    Apologies, not a solution as such but as you have not given any examples etc, it's quite difficult to know of context and therefore exact solution.


    Author Comment

    Thank you for your response Tony.  I am sure your XML solution is more elegant than VBA. Unfortunately for the app I am using I have to use VBA to take data from a stored procedure and insert it into fields in a pdf form.  I just need the basic VBA code for doing this sort of thing and then I can take it from there and award the points. Hypothetically let's say my stored procedure returns peoples first names, how do I get the name into the adobe field. The adobe fields is f1_01(0) and the stored procedure field is called name. Is it something like this?:
    jso.getField(f1_01(0)).Value = name


    Author Comment

    Thanks zorvek. I wasn't sure how to add zones.  Waiting patiently.

    Author Comment

    I do know how to read data from a stored procedure into Excel.  The link you sent may do the other part of it.
    I will let you know tomorrow and award points.
    Signing off for the night.
    LVL 15

    Accepted Solution

    here's what i found in my search...

    you need adobe acrobat V7 plus ( the writer not the reader)..

    then in vb6/vba you can call an adobe acrobat object and use some JSON to write data from office into an adobe pdf form.

    there seems to be distiller and writer, 2 different systems from adobe for making forms...not sure if that has an effect.

    here's the urls my research turned up, hope this helps:

    In order to achieve this we have to make a reference in the excel program to the Adobe Acrobat 6.0 Type Library ( acrobat tlb). 
     the Excel Macro code example ( in which a field, named P1.REP in the PDF document will be set to the value "test test test" ) ( this macro is triggered in the Excel tax calculation program ) 
     Private Sub UpdatePDF()
     Dim gApp As Acrobat.CAcroApp
     Dim pdDoc As Acrobat.CAcroPDDoc
     Dim jso As Object
     Set gApp = CreateObject("AcroExch.App") ' make acrobat session
     Set pdDoc = CreateObject("AcroExch.PDDoc")
     If pdDoc.Open("C:\2762deel1-aang-N.c.pdf") Then ' the PDF document we want to fill in open it
     Set jso = pdDoc.GetJSObject ' set the Javascript object via this way we can fill in the PDF document fields
     If Not jso Is Nothing Then
     jso.getField("P1.REP").Value = "test test test " ' ' jso can be interpreted as" this" in javascript thus being a doc. The same javascript functions can be used in here.
     End If
     pdDoc.Save PDSaveIncremental, path ' save changes to the PDF document
     pdDoc.Close ' close the PDF document
     MsgBox ("Failed could not open PDF ")
     End If
     Set pdDoc = Nothing ' clear
     End Sub
     The "problem" is that this macro will only run on a machine where Acrobat is installed ( Reader or D istiller is not enough )

    Open in new window

    LVL 15

    Expert Comment

    by:Simon Ball acrobat software required.

    Author Closing Comment

    Thank you!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now