How to enlist Excel VBA macros (QTP, VBScript)

Posted by Albert Gareev on Jan 20, 2010 | Categories: How toMS Excel Data

Parent page: Service Functions – Excel (QTP, VBScript)



Connect to Excel through COM – Open workbook – Retrieve Code Module object – Retrieve Code Block string – Parse the string – close and save the workbook.


For this example let’s assume our target Workbook is stored as “C:\Temp\1.xls”, and our target Code Module is named “Module1”, and we’re looking for “Public” macros only.

QTP/VBScript source code

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False
Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")
Set objCodeModule = XLBook.VBProject.VBComponents.Item("Module1").CodeModule
sCodeBlock = objCodeModule.Lines(1, objCodeModule.CountOfLines)

'sCodeBlock now contains the all code lines from Module1

Set objMacros = CreateObject("Scripting.Dictionary")

'Here we'll store captured macro declarations

intStart = 1

Do While True
 intIndex = InStr(intStart, sCodeBlock, "public", vbTextCompare)
 If intIndex = 0 Then
  Exit Do
 End If

 intLineEnd = InStr(intIndex, sCodeBlock, VbCrLf, vbTextCompare)
 sHeader = Mid(sCodeBlock, intIndex, intLineEnd-intIndex+2)
 objMacros.Add objMacros.Count+1, sHeader

 intStart = intIndex + 1
'See sample Excel VBA Code Module below
'objMacros.Item(1) contains "Public Function TestFunction()"

'objMacros.Item(2) contains "Public Function TestFunction2(ByVal intRow)"

'objMacros.Item(3) contains "Public Sub TestSub()"



Set objMacros = Nothing
Set objCodeModule = Nothing

Set XLBook = Nothing
Set XLHandle = Nothing

Excel/VBA source code

Public Function TestFunction()
 Sheet1.Cells(1, 1) = "Executed function"
End Function

Public Function TestFunction2(ByVal intRow)
 Sheet1.Cells(intRow, 1) = "Changed Row " & intRow
End Function

Public Sub TestSub()
 Sheet1.Cells(2, 1) = "Executed sub"

End Sub

  • One response to "How to enlist Excel VBA macros (QTP, VBScript)"

  • Rajshekhar
    14th June 2010 at 9:30


    Plse can you help me how to write macro’s to auto update the test data for each round of QTP script execution.

    Kind Regards

    [ Albert’s reply.
    Hi Raj,
    I see the following breakdown of your question.
    1) How to write VB macro?
    2) How to update test data for a script?

    Answer for (1) is: learn programming concepts and learn VBA/VBScript
    (2) calls for the questions regarding a context:

    • WHERE you want your test data to be updated (Excel workbook, QTP Datatable object) ?
    • WHAT did you mean under “auto update” (automatically generate, pull out of other resource, capture from GUI) ?

    You need to think on these questions, and prepare detailed, context-specific answers.
    That’ll help you answering your question.
    Should you have a particular problem building an algorithm or with coding, feel free to get back to me.

    Albert ]

  • Leave a Reply

    * Required
    ** Your Email is never shared

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.