Automating Abobe Pro with Excel. Pie In The Sky?

[Ark68]

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]

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.

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.