Automating Abobe Pro with Excel. Pie In The Sky?

Ark68

Well-known Member
Joined
Apr 5, 2005
Messages
3,240
I have written (taken me years) an Excel based VBA "application" that allows a user to access and combine data from external databases, provides an interface for the user to edit, modify and enhance that data, and initiate Word mail merges using that data. Fundamentally it works ok. The mail merge creates individual documents wqhich are printed and distributed to groups of users for reference and action based on the data presented to them. The documents are returned, reviewed, with the initial database accessed and contents edited based on returned information on the documents.

It an effort to reduce the masses of paper wasted with this process (daily there can be dozens of pages printed), it has been decided to go electronic using Adobe fillable form technology on an iPad. Adobe Acrobat Pro, from what I have seen from playing around (I have to self teach myself) allows me to use the existing Word mail merge document as a basis of my Adobe form. I can adapt the Word document's end user (paper receiving) documentable fields to be fillable from with Adobe Reader on the tablet. Once the Adobe form is created, I can do a "mail merge" of sorts from within Adobe.

My question is this ... I can still utilize my Excel VBA based application to extract and modify data for merging into a document, but is it possible to automate the Adobe merge and document creation from Excel, much like I do with Word Mailmerge. ie. a button push will launch Adobe Pro and initiate it's mail merge feature to create the fillable form for distribution? Perhaps this is a big stretch of power, but what was once an efficient application but wasteful, is being replaced by inefficiency in processing with no waste.

Any thoughts on approach, online research resources (appropriate Google search terms perhaps), critical or optimistic feedback would be greatly appreciated.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
I'm not sure what sort of mail merge Acrobat Pro provides, but this VBA code shows how to write a value to a PDF form field.
VBA Code:
Public Sub Write_PDF_Form_Field()

    Dim PDdocForm As Object
    Dim JSO As Object
    Dim PDFformFile As String
    Dim field As Object
    
    PDFformFile = "C:\folder\path\PDF form.pdf"
        
    Set PDdocForm = CreateObject("AcroExch.PDDoc")
    PDdocForm.Open PDFformFile
    
    Set JSO = PDdocForm.GetJSObject
    Set field = JSO.getField("FieldName")
    field.Value = "Field Value"
    
    PDdocForm.Save 1, Replace(PDFformFile, ".pdf", "2.pdf")   'Save with new file name
    PDdocForm.Close
      
End Sub
You could expand this code to loop through the rows in a sheet and write cell values to multiple fields in a PDF form.
 

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top