Connecting to excel as database using Adodb in VBScript/QTP

We can connect to excel file using Adodb object in vbscript.  Following are the steps in working with excel as database.

1.       Create an instance of connection :

Set objConn = CreateObject("Adodb.connection")

2.       Create connection string to connect to excel database as follows

strCon = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;Dbq=" & Workbook &";"

Here we have created a database for excel Workbook . The sheets in the workbook acts as table of the database.

3.       Connect to database as shown below:

objConn.open strCon

Now we are done with the connection to excel , we require SQL to run on the data to extract required information . 

4. Create a SQL as required as shown below:

strSQL =  "select name from [Table1$];"

Note here, we have to give sheet name acting as table within square bracket and with suffix “$” as shown above.

5.   Create a recordset as shown below and execute the query using recordset, all  the records based on query will be stored in recordset.

Set rs = CreateObject("ADODB.recordset")

rs.Open strSQL,objConn

6.       We can transfer the data extracted from query back in excel using copyrecordset method

XLSheet.range("A2").copyfromrecordset rs

Where XLSheet is the reference of required sheet in the excel  file and A2 is the location in sheet from which data will be pushed in the sheet.