EDN Admin
Well-known member
This may be more of a question for Adobe, but Im at my wits end and think just about anyone with VB experience would be able to tell me what Im doing wrong.<br/>
Suffice to say, I know very little about VB (or any of the other languages behind the software), but Ive adapted code which has allowed me to get almost everything I need done, thus far.
Im 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 Ive 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 buttons name, in Acrobat, is Photo1. Ive several SendKeys commands in order to save
each file with a unique, row specific name.
<pre class="prettyprint 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()
Application.Photo2_Click
End Sub
this was an attempt to setup a sub which Id 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 & _
"%%EO"
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")
Else
sFileFields = Replace(sFileFields, "Cond-Excellent", vbNullString)
End If
If vClient(x, 28) = "G" Then
sFileFields = Replace(sFileFields, "Cond-Good", "Yes")
Else
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
DoEvents
Open FDF file as PDF
ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
Application.Wait Now + TimeValue("00:00:04")
Application.Photo2.Focus
PDF_FILE.Photo2.Focus
Application.Photo2_Click
Application.SetButtonIcon "Photo1", ActiveWorkbook.Path & "Exports" & "vClient(x, 1)" & "-1.pdf", 0
Application.Field.SetFocus "Photo1"
Call ClickMe
above is where im trying to click the button, although Id 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}")
SetForegroundWindowap
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
Loop
Exit Sub
ErrorHandler:
MsgBox "Export_Worksheet Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
End Sub
[/code]
Im pretty sure one of many issues is that I dont 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 its even possible, is clumsy and unfounded, but I am (obviously) flailing around for anything that can achieve clicking this confounded button. Any help appreciated.
<br/>
<br/>
View the full article
Suffice to say, I know very little about VB (or any of the other languages behind the software), but Ive adapted code which has allowed me to get almost everything I need done, thus far.
Im 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 Ive 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 buttons name, in Acrobat, is Photo1. Ive several SendKeys commands in order to save
each file with a unique, row specific name.
<pre class="prettyprint 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()
Application.Photo2_Click
End Sub
this was an attempt to setup a sub which Id 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 & _
"%%EO"
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")
Else
sFileFields = Replace(sFileFields, "Cond-Excellent", vbNullString)
End If
If vClient(x, 28) = "G" Then
sFileFields = Replace(sFileFields, "Cond-Good", "Yes")
Else
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
DoEvents
Open FDF file as PDF
ShellExecute vbNull, "open", sFileName, vbNull, vbNull, SW_NORMAL
Application.Wait Now + TimeValue("00:00:04")
Application.Photo2.Focus
PDF_FILE.Photo2.Focus
Application.Photo2_Click
Application.SetButtonIcon "Photo1", ActiveWorkbook.Path & "Exports" & "vClient(x, 1)" & "-1.pdf", 0
Application.Field.SetFocus "Photo1"
Call ClickMe
above is where im trying to click the button, although Id 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}")
SetForegroundWindowap
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
Loop
Exit Sub
ErrorHandler:
MsgBox "Export_Worksheet Error: " + Str(Err.Number) + " " + Err.Description + " " + Err.Source
End Sub
[/code]
Im pretty sure one of many issues is that I dont 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 its even possible, is clumsy and unfounded, but I am (obviously) flailing around for anything that can achieve clicking this confounded button. Any help appreciated.
<br/>
<br/>
View the full article