How to access the excel as a database using adodb.connection in QTP?

We can work with excel in 3 ways in QTP as mentioned below.

1. Using Excel.Application
2. Using Adodb.Connection
3. Using DataTable

To know how we can use Excel.Application to read or write the excel files, you can refer these links.

To know how to use datatable to load excel into it, use these links.

In this article we will see how we can read or write excel files using Adodb.Connection object.
Excel workbook is considered as a database. Each sheet in the excel workbook is considered as a table.
First row in the sheet is considered as column header and all other rows are considered to be records.

Here is the sample code to connect to excel database.

Set excelConnection = createobject("Adodb.Connection")
excelConnection.open "Data Source=c:\abc.xlsx;Provider=Microsoft.Jet.OLEDB.4.0"
Set rs = excelConnection.execute "Select * from [sheet1$]"
For i=0 to rs.recordCount-1
For j=0 to rs.fields.count-1
print rs.fields(j).name & rs.fields(j).value
Next
Next

Please give your inputs, suggestions, feedback to Us about above QTP topic. We value your thoughts.