How to use Dictionary object for storing Excel data row (QTP, VBScript)
Parent page: Service Functions – Excel (QTP, VBScript)
Answers
Description
Connect to Excel through COM – Open workbook – Open worksheet – map cell values to column names.
Notes.
- 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”)
'Assumptions:
'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
Next
One response to "How to use Dictionary object for storing Excel data row (QTP, VBScript)"
Hi,
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.]