All related posts: Reference Page – GP/QTP Automation  

VBScript limitation 

Since VBscript supports only Variant type attempts to use variable in a COM call with an argument defined as “[in, out] BSTR * “ reasonably end with “Type mismatch” error. This error is generated by the COM object itself rather then VBscript interpreter since the object gets string instead of a pointer.

ExecuteSanScript method uses “string pointer” for parameter exchange which is not supported in VBScript that has only and single “variant” data type. Passing in VBScript variables causes “Type Mismatch”.

There are 3 ways to overcome the identified limitation.

• Do not use “string pointer” type in the COM function. – Not applicable way in case of QTP/GP automation as we have the vendor provided code.
• Implement in C++ (or other similar language) COM compatible DLL module with the all library functions directly operating with Dynamics.Application object and serving as a wrapping bridge to convert “string pointer” type to “variant” and backward.
• Use existing COM object that may act both as Automation Server and Automation Client.

Excel.Application object is a good candidate as it has built-in internal Excel.VBA execution engine.

Documentation 

The following portal could be useful both for beginners and advanced Excel.VBA programmers and users: http://www.functionx.com/excel/index.htm 

Excel Object Model Overview from Microsoft contains full description and some examples

http://msdn.microsoft.com/en-us/library/wss56bz7(VS.80).aspx 

Creating Excel macro module 

1)     Start Excel application and create new workbook

2)     Go to “Main Menu \ Tools \ Macro \ Visual Basic Editor”

3)     Right-click on the VBA Project panel and insert Code Module and Class Module

4)     Select Class Module and on the Properties panel define its name: ParamHandlerClass. Get to the Code Editor and on the “General/Declarations” section define (type in or copy/paste) parameter names:

Public Value1 As String
Public Value2 As String
Public Value3 As String
Public Value4 As String

5) Select Code Module and get to the Code Editor. Copy the following code.

Option Explicit

Public GPApp As Object

Public ParamHandler As ParamHandlerClass

Public Function Init()
    Dim intRC
    Set GPApp = GetObject("", "Dynamics.Application")
    Set ParamHandler = New ParamHandlerClass
    intRC = GPApp.SetParamHandler(ParamHandler)
End Function

Public Function Done()
    Set GPApp = Nothing
    Set ParamHandler = Nothing
End Function

Public Function GetDexActiveWindow()
    Dim intRC As Integer
    Dim sFormName As String
    Dim sWindowName As String
    Dim sDictName As String
    Dim sTitle As String

    'Clean
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 1) = ""
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 2) = ""
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 3) = ""
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = ""

    'Call method
    intRC = GPApp.GetDexActiveWindow(sFormName, sWindowName, sDictName, sTitle)

    'Store parameters
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 1) = sFormName
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 2) = sWindowName
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 3) = sDictName
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = sTitle

End Function

'Note.
'SanScript's string data type is limited up to 255 chars
'Therefore contents of the list is retrieved in a loop of calls
'and one by one items are collected in VB
Public Function GetListContent()
    Dim intRC As Integer
    Dim intCount, Iter As Integer
    Dim sCode, sErrMsg As String
    Dim sListName, sResult As String

    sListName = Workbooks.Item(1).Sheets.Item(1).Cells(2, 1)

    'Get list content count
    sCode = "local integer count; count = countitems(" & sListName & "); OLE_SetProperty(""Value1"", str(count));"

    intRC = GPApp.ExecuteSanScript(sCode, sErrMsg)
    If intRC <> 0 Then
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = intRC
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = sErrMsg
        Exit Function
    End If

    intCount = ParamHandler.Value1
    If intCount = 0 Then
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = -1
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = "The list is empty"
        Exit Function
    End If

    'Get first element
    sCode = "OLE_SetProperty(""Value1"", itemname(" & sListName & ", 1));"
    intRC = GPApp.ExecuteSanScript(sCode, sErrMsg)
    If intRC <> 0 Then
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = intRC
        Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = sErrMsg
        Exit Function
    End If

    sResult = ParamHandler.Value1

    'Loop and retrieve the rest
    For Iter = 2 To intCount
        sCode = "OLE_SetProperty(""Value1"", itemname(" & sListName & ", " & Iter & "));"
        intRC = GPApp.ExecuteSanScript(sCode, sErrMsg)
        If intRC <> 0 Then
            Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = intRC
            Workbooks.Item(1).Sheets.Item(1).Cells(2, 4) = sErrMsg
            Exit Function
        End If
        sResult = sResult & Chr(10) & ParamHandler.Value1
    Next

    'Store result
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 5) = 0
    Workbooks.Item(1).Sheets.Item(1).Cells(2, 1) = sResult

End Function

6)     Save and close Excel and Excel.VBA windows.

Next posts will demonstrate examples on using VB macros called by VBScript from within QTP.

If someone experiences difficulties creating Excel file, you can download it.

If you have any additional questions on the code samples presented you’re welcome to put them in comments for this post.