PowerBuilder calling a Word macro
This is a long shot, but I'm running out of research options.
PowerBuilder runs a query and loops through a record set passing field
values to a macro. There are a total of 17 values passed.
PowerBuilder balks when using this syntax
businessunit.Run "PopulateContacts",ls_lead1,ls_fname, ls_lname,
ls_addprimary, ls_cityprimary, ls_stateprimary, ls_zipprimary, ls_empphone,
ls_cell, ls_emergencycontact, ls_addemergency, ls_cityemergency,
ls_stateemergency, ls_zipemergency, ls_phonemergency, ls_cellemergency,
ls_relationprimary
And it balks using this syntax:
businessunit.Run( "PopulateContacts",ls_lead1,ls_fname, ls_lname,
ls_addprimary, ls_cityprimary, ls_stateprimary, ls_zipprimary, ls_empphone,
ls_cell, ls_emergencycontact, ls_addemergency, ls_cityemergency,
ls_stateemergency, ls_zipemergency, ls_phonemergency, ls_cellemergency,
ls_relationprimary)
We're running macros that do not have arguments and have not encountered and
diffuculties. Below is the macro code that resides in the Word template:
'-----------------------------------------------------------------
' Procedure : PopulateContacts
' Type : Sub
' Author : Kevin Backmann
' Date : 9/23/2008
' Purpose : Contacts require a great deal of work in regards to
' the assembly of a standard block address based upon
' first name, last name, address, city, state & zip.
'
' For this reason, this subroutine was written to
' eliminate the need to do all this work on the
' PowerBuilder side.
' Input : EmpLead, EmpFirstName, EmpLastName, EmpAddress,
' EmpCity, EmpState, EmpZip, EmpPhone, EmpCell,
' ConFirstName, ConLastName, ConAddress, ConCity,
' ConState, ConZip, ConPhone, ConCell and
' ConRelationship
' Output : Nothing
'-----------------------------------------------------------------
'
Sub PopulateContacts(EmpLead As String, EmpFirstName As String, _
EmpLastName As String, EmpAddress As String, _
EmpCity As String, EmpState As String, _
EmpZip As String, EmpPhone As String, _
EmpCell As String, ConName As String, _
ConAddress As String, ConCity As String, ConState As String, _
ConZip As String, ConPhone As String, ConCell As String, _
ConRelationship As String)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Assorted Word object variables
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim doc As Document, bkm As Bookmark, tbl As Table
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Column and row counts of the Contact List table and yet
' another counter variable
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim intColumns As Integer, intRows As Integer, i As Integer, _
intVal As Integer
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' String variables for the components that make up the
' address block for the employee and their phone numbers
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim intLead1 As Integer, strLead1 As String, strEmpName As _
String, strEmpStreet As String, strEmpCityStateZip As _
String, strEmpAddressBlock As String
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' String variables for the components that make up the
' address block for the employee contact and their phone
' numbers
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim strConName As String, strConStreet As String, _
strConCityStateZip As String, strConAddressBlock As _
String
Dim strVals(7) As String
On Error GoTo Err_PopulateContacts
Set doc = ThisDocument
Set bkm = doc.Bookmarks("Contact_List")
bkm.Select
Set tbl = Selection.Range.Tables(1)
intColumns = tbl.Columns.Count
intRows = tbl.Rows.Count
'-------------------------------------------------------------
' The strLead1 value is number passed as text and will be a
' value of 1, 2, 3, 4 or 5. Convert the text number to a
' value and use the select statement to evaluate the emp's
' category (Lead, Alt1, Alt2, Emp or Alt3)
'-------------------------------------------------------------
If IsNumeric(EmpLead) Then
intLead1 = CInt(EmpLead)
Else
intLead1 = 0
End If
Select Case intLead1
Case 1: strLead1 = "Lead"
Case 2: strLead1 = "Alternate 1"
Case 3: strLead1 = "Alternate 2"
Case 4: strLead1 = "Employee"
Case 5: strLead1 = "Alternate 3"
Case Else: strLead1 = ""
End Select
'-------------------------------------------------------------
' Next, assemble the address block for the employee using
' the sub arguments with an 'Emp' prefix.
' EmpFirstName is last name concatenated to first name and
' separated by a comma
'-------------------------------------------------------------
If Len(EmpFirstName) > 0 Then
strEmpName = Trim$(EmpLastName & ", " & EmpFirstName)
Else
strEmpName = Trim$(EmpLastName)
End If
strEmpStreet = Trim$(EmpAddress)
If Len(strEmpStreet) > 0 And Len(strEmpName) > 0 Then
strEmpStreet = strEmpName & vbCrLf & strEmpStreet
End If
If Len(EmpCity) > 0 Then
strEmpCityStateZip = EmpCity
End If
If Len(EmpState & " " & EmpZip) > 0 Then
If Len(strEmpCityStateZip) > 0 Then
strEmpCityStateZip = Trim$(strEmpCityStateZip & _
", " & Trim$(EmpState) & " " & Trim$(EmpZip))
End If
End If
If Len(strEmpStreet) > 0 And Len(strEmpCityStateZip) > 0 Then
strEmpAddressBlock = strEmpStreet & vbCrLf & _
strEmpCityStateZip
Else
strEmpAddressBlock = strEmpStreet
End If
'-------------------------------------------------------------
' Assign the employee information to the first 4 positions
' in the array.
'-------------------------------------------------------------
strVals(0) = strLead1
strVals(1) = strEmpAddressBlock
strVals(2) = FormatPhone(EmpPhone)
strVals(3) = FormatPhone(EmpCell)
'-------------------------------------------------------------
' Next, assemble the address block for the employee contact
' using the sub arguments with a 'Con' prefix.
' EmpFirstName is last name concatenated to first name and
' separated by a comma
'-------------------------------------------------------------
strConName = Trim$(ConName)
strConStreet = Trim$(ConAddress)
'-------------------------------------------------------------
' Very the lengths of each line segment of the contact's
' address to determine where to place carriage returns in
' the concatenated address block.
'-------------------------------------------------------------
If Len(ConCity) > 0 And Len(ConState & " " & ConZip) > 0 Then
strConCityStateZip = Trim$(ConCity) & ", " & _
Trim$(ConState) & " " & Trim$(ConZip)
Else
strConCityStateZip = Trim$(ConState) & " " & Trim$(ConZip)
End If
If Len(strConName) > 0 And Len(strConStreet) > 0 Then
strConAddressBlock = strConName & vbCrLf & strConStreet
Else
strConAddressBlock = strConStreet
End If
If Len(strConAddressBlock) > 0 And Len(strConCityStateZip) > _
0 Then
strConAddressBlock = strConAddressBlock & vbCrLf & ConCity
End If
'-------------------------------------------------------------
' Assign the employee contact information to the next
' 4 positions in the array.
'-------------------------------------------------------------
strVals(4) = strConAddressBlock
strVals(5) = FormatPhone(ConPhone)
strVals(6) = FormatPhone(ConCell)
strVals(7) = ConRelationship
'-------------------------------------------------------------
' Cycle through the array and place each array value in
' the last row, using the counter variable as the column
' number. Add another row when done so it's available the
' next time this the tableis populated.
'-------------------------------------------------------------
For i = 1 To 8
intVal = i - 1
tbl.Cell(intRows, i).Range.Text = strVals(intVal)
Next i
tbl.Rows.Add
Exit_PopulateContacts:
'Release all object variables and gracefully exit
Set tbl = Nothing
Set bkm = Nothing
Set doc = Nothing
Exit Sub
Err_PopulateContacts:
'Generic error routine
MsgBox "Error Number:" & Err.Number & vbCrLf & vbCrLf & _
"Error Description: " & Err.Description & vbCrLf & _
"Error occurred in the sub named PopulateContacts."
Err.Clear
Resume Exit_PopulateContacts
End Sub
We could populate the table entirely from the PowerBuilder side, but it
takes quite a bit of time and I was hoping to get the document populated
quickly by calling the Word piece from the Word template.
I know this is a bit out of the ordinary and I would appreciate any comments
or suggestions that you might have.
Thanks ahead of time.
--
Kevin Backmann
date: Mon, 6 Oct 2008 11:09:11 -0700
author: Kevin B Bgone
