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).
