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

Below example demonstrates how we can access the excel workbook as a Database using ADODB.Connection object.

Set objConnection = createobject("ADODB.CONNECTION")
'Set objRecordSet = createobject("ADODB.RECORDSET")
objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
objConnection.ConnectionString = "Data Source=C:\mybook1.xls;Extended Properties=Excel 8.0;"

'objConnection.Open "Data Source=excelDSN"
'We can also provide the Data Source in DSN style as mentioned above. excelDSN is the DSN I have created for the excel workbook using ODBC

'Open the database connection
objConnection.Open



'Execute sql query using connection object
'Please note how we have written the query to get data from sheet. "AA" is the name of the sheet.
'Note that you have to give the name of the sheet inside square brackets with $ as the last character.
'If you are having column names in your query you will have to put them inside square brackets as well.

 Set  objRecordSet = objConnection.Execute( "Select * from [AA$]")

 'recordcount property of the recordset object does not work in Excel So we have used EOF property to check the end of records.
Do While (not objRecordSet.EOF)

If  not  isnull(objRecordSet.Fields(8).Value) Then
print objRecordSet.Fields(8).Value
End If

'Move to next record in sheet - AA
objRecordSet.MoveNext

Loop

'Close the adodb connection
objConnection.Close

Advantages of using Excel as a Database.

1. We can access one row at a time. So memory consumption is less
2. Faster access to records.

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