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
