GP/QTP Automation: Execute Dexterity Macro

Posted by Albert Gareev on Dec 23, 2009 | Categories: Great PlainsSource code

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


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

'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), "\", "/")

boolRC = GPHandle.RunDexterityMacro(sDexCallName)

If boolRC Then
' Put your reporting function here "Dexterity Macro execution successful"
' 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).

  • 2 responses to "GP/QTP Automation: Execute Dexterity Macro"

  • Joen Baldwin
    21st February 2010 at 5:47

    I want to thank the blogger very much not only for this post but also for his all previous efforts. We called HP support and they said we can’t use QTP for Great Plains automation. I found automation-beyond the only resource on the Web for GP automation testing.
    It’s not that we mindlessly used all the code examples. It’s a great example of problem solving approach that inspired us.

    Please keep up the awesome work and continue to post topics like this.

    [ Albert’s Reply.
    Thank you, Joen.
    Your appreciation is inspiring for me too. ]

  • Mitch
    21st April 2010 at 17:38

    I was just wondering WHY Dex doesn’t allow any parameterization?

    [Albert’s reply. I guess, it is internally available. Taken off later.]

Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported
This work by Albert Gareev is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported.