Treat excel as database and fetch the data based on a query
'Define variables
Dim objCon, objRecordSet, strExlFile,
colCount, row, i
'Set Connection object
Set objCon = CreateObject("ADODB.Connection")
'Set Recordset object
Set objRecordSet = CreateObject("ADODB.Recordset")
'Path of excel file
strExlFile = "C:\QTP
Practice\Excel as Database\TestData.xls"
'Connection string
objCon.Open "DRIVER={Microsoft
Excel Driver (*.xls)};DBQ=" &strExlFile
& ";Readonly=True"
'OR you can use below line for connection string
'objCon.Open
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xls;Extended
Properties=Excel 12.0;Persist Security Info=False"
'Set cursor type - Very Inportant. If you fail to
set it, then you will not get the correct row count
objRecordset.CursorType = 1
'Query to fetch the data
strSQLStatement = "SELECT
* FROM [Data$]"
'Create recordset
objRecordSet.Open strSQLStatement, objCon
'Get coulumn count
colCount = objRecordSet.Fields.count
msgbox colCount
'Get row count
rowCount = objRecordSet.RecordCount
msgbox rowCount
'Display value in each row
While objRecordSet.EOF=false
row=""
For i=0 to colCount-1
row =
row &"
"& objRecordSet.fields(i)
Next
msgbox row
objRecordSet.moveNext
Wend
'Release objects
Set objRecordSet = Nothing
objCon.Close
Set objCon = Nothing
Comments