Using excel sheet as database table

Excel sheet can be used as a database for the parameterization purpose. Following code demonstrate how to connect and consider excel sheet as database table.
This might be usefull while working with databases. You can export database table into excel (one time) and then work on excel as database.

Dim objCon, objRecordSet, strExlFile, colCount, row, i

Set objCon = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

strExlFile = “C:abhikansh.xls”

objCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=” &strExlFile & “;Readonly=True”

strSQLStatement = “SELECT * FROM [Sheet2$]”

objRecordSet.Open strSQLStatement, objCon ‘create recordset

colCount = objRecordSet.Fields.count    ‘No of columns in the table

While objRecordSet.EOF=false
    For i=0 to colCount-1
        row=row &”    “& objRecordSet.fields(i)
    Print row
Set objRecordSet = Nothing
Set objCon = Nothing

Screen-shots of sample excel and output are following –

In case of any queries, please post your comments.

3 thoughts on “Using excel sheet as database table”

  1. Hi Abhikansh,
    I was wondering that the above code should work fine even without adding the for loop after while loop as recordset object would point to first row by default and would keep looping till the condition becomes true.
    also, just a suggestion, it would be great if you can print the output to show the format of it.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top