All related posts: Reference Page – GP/QTP Automation 

Dexterity Macro Limitation and Suggested Workaround

One of the major limitations identified in Dexterity Macro System is totally hard-coded data with no functionalities to read them from any external source. To overcome this issue we can dynamically generate Dexterity macro text file from a pre-built XML template. After the file is generated controller script (driven by QTP) may invoke it through COM interface.

VBA/VBScript functions

VBA function – put in your Excel VBA macro file

Note. Stores error code and error description (if) returned.

Public Function RunDexterityMacro()
    Dim intRC As Integer
    Dim sCode, sErrMsg As String
    Dim sPath

    sPath = Workbooks.Item(1).Sheets.Item(1).Cells(2, 1)
    sCode = "run macro " & """" & sPath & """" & ";"
   
    Call GPApp.Activate
    intRC = GPApp.ExecuteSanScript(sCode, sErrMsg)
    If intRC <> 0 Then
        'Store result
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = intRC
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = sErrMsg
        Exit Function
    End If
   
    'Clear result
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = 0
   
End Function

VBScript function – put as Dexterity Interface Class method

Note. If you use it as an embedded function don’t forget to call COM initialization procedures first.

Public Function RunDexterityMacro(ByVal sMacroName)
Dim boolRC, intRC
Dim objUsedRange, objRecord

Set objUsedRange = XLBook.Worksheets(1).UsedRange()
objUsedRange.Cells(2,1) = sMacroName

XLHandle.Run("RunDexterityMacro")

Set objRecord = RetrieveParameters()
intRC = objRecord.Item("Var5")
If intRC <> 0 Then
RunDexterityMacro = FALSE
End If

RunDexterityMacro = TRUE
End Function

A function to execute Dexterity Macro
sTemplateName – full path and name of the XML template file
sMacroName – Dexterity Macro file to create

Uses Service Functions – System (QTP, VBScript)

Public Function GP_RunMacro(ByVal sTemplateName, ByVal sMacroName)
Dim boolRC, intRC
Dim sFullPath, sSubfolder
Dim sLogFile
Dim sCallName, sDexCallName, sDrive
Dim FSO

'Check file exists
Set FSO = CreateObject("Scripting.FileSystemObject")
boolRC = FSO.FileExists(sTemplateName)
If Not boolRC Then
Set FSO = Nothing
' Put your reporting function here "Failed to locate template file " & sTemplateName
Exit Function
End If

'Create subfolder
sFullPath = SubfolderCreate(sMacroName, sSubfolder)

'Substitute GP Macro Log filename
sLogFile = sFullPath & sMacroName & "_log.txt"
sLogFile = Replace(sLogFile, "\\", "\")
sDrive = ":" & Left(sLogFile, 2)
sLogFile = sDrive & Replace(Mid(sLogFile, 4), "\", "/")

'You should store sLogFile in the data source object according to your data model

'Generate macro file
sCallName = sFullPath & sMacroName & ".mac"
sCallName = Replace(sCallName, "\\", "\")
boolRC = GenerateTextFile(sTemplateName, sCallName)

If Not boolRC Then
Set FSO = Nothing
' Put your reporting function here "Failed to generate macro" & sCallName
Exit Function
End If

sDrive = ":" & Left(sCallName, 2)
sDexCallName = sDrive & Replace(Mid(sCallName, 4), "\", "/")

'Execute
boolRC = GPHandle.RunDexterityMacro(sDexCallName)

'reporting
If boolRC Then
'PASS
' Put your reporting function here "Dexterity Macro execution successful"
Else
'FAIL
' Put your reporting function here "Dexterity Macro execution failed"
End If

End Function

Log file, created by Great Plains Dexterity, will contain all response messages and screen dump data (if you used the according macro commands).