Quickly import excel to Quicktest
Posted by admin - Mar 30, 2009 Articles, Olga Ivolgin 0 0 Views : 261 Receive Updates For This Category
Article Tools
- Print this page
- Add Comment
- Send to Friend
- Last Updated on :
Jul 15, 2011
A Russian translate for is article is available here.
Olga Ivolgin gives us a wonderfully useful QTip – quickly import an excel datasheet with the column names!
In automatic testing of big projects always appears requirement for fast "fence" of the data from Excel file in any proper place.
As a rule, the first line contains the name of parameters, and the subsequent lines contain the data for test variations. I offer you a way which for seven seconds will return the necessary data with the names of columns.
Variable="ENV"
Icount=0 ‘Zeroing items count
Isheet= "Sheet1" '''sheet name
ExcelPath="D:\LifeInsurance\Excel_Files\Version\30466\OL.xls"
Redim Person(1,0) '''create a two – dimensional array
Row=5
Call FuncGetValueFromExcellFile(ExcelPath,Isheet,Person,Row,Icount) '''call to function
'search for MyValue in the Person dimensional array :
For i=0 to Icount-1
If Person(0,i)= Variable Then
MyValue=Person (1,i)
End If
Next
Function FuncGetValueFromExcellFile(ExcelPath,Isheet,Person ,Row,Icount)
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Workbooks.Open ExcelPath
Set DetailsSheet = ExcelObj.Sheets.Item(Isheet)
RowDetals=DetailsSheet.rows(1).Value
RowDetails2=DetailsSheet.rows(Row).Value
Set dIcBook=CreateObject("Scripting.Dictionary")
A=dIcBook.RemoveAll
For j=1 to 256
Val1= RowDetals(1,j )
If Val1=""Then
ColumnNum=j
Exit for
End If
Val2=RowDetails2(1,j )
dIcBook.Add Val1 ,Val2
Next
ExcelObj.Application.Quit
Set DetailsSheet = Nothing
Set ExcelObj = Nothing
AllKyes = dIcBook.Keys
AllItems= dIcBook.Items
For k=0 to ColumnNum - 2
person(0,k) = AllKyes(k)
person(1,k) = AllItems(k)
Redim preserve person(ubound (person, 1), ubound (person, 2) + 1)
Next
Icount=dIcBook.count
A= dIcBook.RemoveAll
Set dIcBook=Nothing
End Function
Thanks Olga for this great QTip!
