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

Popular Posts

Demystifying Automation Frameworks: A Comprehensive Guide to Building Scalable Solutions

Guide to Database Testing

Mastering Java Collections: Your Secret Weapon for Robust Automation Frameworks

The Singleton Pattern in Test Automation: Ensuring Consistency and Efficient Resource Management

Key Differences Between Different Programming and Scripting Languages