How can I click Acrobat Form button from VB (Excel Macro)? RRS feed

  • Question

  • This may be more of a question for Adobe, but I'm at my wit's end and think just about anyone with VB experience would be able to tell me what I'm doing wrong.

    Suffice to say, I know very little about VB (or any of the other languages behind the software), but I've adapted code which has allowed me to get almost everything I need done, thus far.

    I'm trying, desperately, to finalize a Macro which enables me to export a lot of Excel info into individual Acrobat Forms and save them all independently. This all works fine, but there is one last thing I've not been able to accomplish: I need to remote click (or 'focus on') a button in the Acrobat form in order to select the icon button (dynamically set image relevant to each individual form, base on excel cell). The button's name, in Acrobat, is 'Photo1'. I've several SendKeys commands in order to save each file with a unique, row specific name.

    Option Explicit
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Private Const SW_NORMAL = 1
    Public Const PDF_FILE = "Louisiana_Historic_Resource_Inventory Worksheet.pdf"
    Public Sub ClickMe()
    End Sub
    'this was an attempt to setup a sub which I'd call later... 
    'all of the below stuff works fine- fills out the form, checks boxes, etc. as necessary
    Public Sub Export_Worksheet()
        Dim sFileHeader As String
        Dim sFileFooter As String
        Dim sFileFields As String
        Dim sFileName As String
        Dim sTmp As String
        Dim lngFileNum As Long
        Dim vClient As Variant
        Dim x As Integer
        ' Builds string for contents of FDF file and then writes file to workbook folder.
        On Error GoTo ErrorHandler
        x = 1
        sFileHeader = "%FDF-1.2" & vbCrLf & _
                      "%âãÏÓ" & vbCrLf & _
                      "1 0 obj<</FDF<</F(" & PDF_FILE & ")/Fields 2 0 R>>>>" & vbCrLf & _
                      "endobj" & vbCrLf & _
                      "2 0 obj[" & vbCrLf
        sFileFooter = "]" & vbCrLf & _
                      "endobj" & vbCrLf & _
                      "trailer" & vbCrLf & _
                      "<</Root 1 0 R>>" & vbCrLf & _
        vClient = Range(ActiveSheet.Cells(989, 1), ActiveSheet.Cells(989, 90))
        Do While vClient(x, 1) <> vbNullString
        sFileFields = "<</T(Street Number)/V(---Street_Num---)>>" & vbCrLf & "<</T(Street Direction)/V(---Street_Dir---)>>"
    ''''''''''''theres a TON of the above correlations, all in the same format
            If vClient(x, 28) = "E" Then
            '     sTmp = Replace(vClient(1, 3), "-", "")
                sFileFields = Replace(sFileFields, "Cond-Excellent", "Yes")
                sFileFields = Replace(sFileFields, "Cond-Excellent", vbNullString)
            End If
            If vClient(x, 28) = "G" Then
                sFileFields = Replace(sFileFields, "Cond-Good", "Yes")
                sFileFields = Replace(sFileFields, "Cond-Good", vbNullString)
            End If
    ''''''''''''theres another TON of the above replacements, all in the same format
            sTmp = sFileHeader & sFileFields & sFileFooter
            ' Write FDF file to disk
            If Len(vClient(x, 1)) Then sFileName = vClient(x, 1) Else sFileName = "FDF_DEMO"
            sFileName = ActiveWorkbook.Path & "\Exports\" & sFileName & ".fdf"
            lngFileNum = FreeFile
            Open sFileName For Output As lngFileNum
            Print #lngFileNum, sTmp
            Close #lngFileNum
            ' Open FDF file as PDF
            ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
            Application.Wait Now + TimeValue("00:00:04")
            'Application.SetButtonIcon "Photo1", ActiveWorkbook.Path & "\Exports\" & "vClient(x, 1)" & "-1.pdf", 0
            'Application.Field.SetFocus "Photo1"
            Call ClickMe
    ''''above is where i'm trying to click the button, although I'd be just as happy if I could 'focus' on the button.
            Application.Wait Now + TimeValue("00:00:02")
            'Application.SendKeys (vClient(x, 1))
            'Application.SendKeys ("-1.pdf")
            'Application.SendKeys ("{ENTER}")
            Application.SendKeys ("%fap")
            Application.Wait Now + TimeValue("00:00:03")
            Application.SendKeys (vClient(x, 1))
            Application.SendKeys ("{ENTER}")
            'If Len(vClient(x, 1)) Then PrintLine (vClient(x, 1)) ' Else sFileName = "_Check-Parcel"
            ''If Len(vClient(x, 1)) Then SendKeys = Len(vClient(x, 1)) Else sFileName = "_Check-Parcel" {ENTER}
            ''ShellExecute vbNull, "GetSaveFileName", sFileName, vbNull, vbNull, SW_NORMAL & vbCrLf
    '        ShellExecute vbNull, "print", sFileName, vbNull, vbNull, SW_NORMAL
            Application.Wait Now + TimeValue("00:00:02")
            Application.SendKeys ("^w")
            'ShellExecute vbNull, "close", sFileName, vbNull, vbNull, SW_NORMAL
            x = x + 1
        Exit Sub
        MsgBox "Export_Worksheet Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
    End Sub

    I'm pretty sure one of many issues is that I don't know the fully-qualified name of the field/button, or how to properly identify it in the Macro.

    I have no doubt that my approach, if it's even possible, is clumsy and unfounded, but I am (obviously) flailing around for anything that can achieve clicking this confounded button. Any help appreciated.

    • Moved by Reed KimbleMVP Wednesday, May 16, 2012 6:49 PM vba quenstion in vb general (From:Visual Basic General)
    Wednesday, May 16, 2012 4:46 PM

All replies

  • The forum you are currently in is the forum  for VB in VB Net

    Try this forum for VBA (Visual Basic for Application, that is the name for the Basic inside ms office)


    Wednesday, May 16, 2012 6:31 PM
  • Thanks, it looks like it was moved to the right place.

    • Edited by josh-codify Wednesday, May 16, 2012 7:12 PM Redundant.
    Wednesday, May 16, 2012 7:11 PM
  • I searched for Adobe VBA and got:


    Do these help? I think the idea is to trigger the creation of a pdf thru code rather than the interface, but these sites might say how to just start it.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Wednesday, May 16, 2012 8:41 PM
  • Rod,

    Thanks for responding. The first link seems to cover a few processes that are similar to the code that already works fine for me.

    The second is a great article, and the Acrobat docs linked within it are helpful, albeit over my head. But, I still don't see any indication of how to 'focus on' or 'click' a field/button. This is really the only thing I'm trying to get help on.

    I think I may be able to get closer if someone could point out how to generate a list of all of the "fully-qualified" field names within an Acrobat Form, so I could at least be referring to the button correctly. Other than the simple name "Photo1", I don't know how to 'map' the button.

    Alternately, I'm considering remaking the buttons, complete with icons in the fashion described in the article. I'm not sure if it will work, but it seems possible...

    Anyone care to help elucidate this problem?



    Friday, May 18, 2012 1:33 PM