How to use Dictionary object for storing Excel data row (QTP, VBScript)

Posted by Albert Gareev on Jun 10, 2010 | Categories: How toMS Excel Data

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



Connect to Excel through COM – Open workbook – Open worksheet – map cell values to column names.

  • Heading and trailing space characters are removed
  • To make mapping not case sensitive index is brought to lower case
  • If your spreadsheet allows column name duplication you need to enhance the presented code by adding unique index to duplicated column names (i.e. “Name, Name_1, Name_2”)


'Excel file exists
'Workbook is open (XLBook)
'Sheet exists
Set objDataRow = CreateObject("Scripting.Dictionary")
sSheetIndex = 1
intTargetRow = 123
Set objUsedRange = XLBook.Worksheets(sSheetIndex).UsedRange()
For Iter = 1 To objUsedRange.Columns.Count
   sCellName = LCase(Trim(objUsedRange.Cells(1,Iter)))
   sCellValue = Trim(objUsedRange.Cells(intTargetRow,Iter))
   objDataRow.Item(sCellName) = sCellValue
   End If

  • One response to "How to use Dictionary object for storing Excel data row (QTP, VBScript)"

  • anonymus
    1st March 2015 at 22:30


    if we do parallel execution of the script on two different machines, then there might be a problem with the source data sheet. As source data sheet will be read only for the another test. so does using “UsedRange” object solve the problem mentioned?

    [Albert’s response:
    Having “write” access for reading Excel data into Dictionary is not required.]

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