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
    row=""
   
    For i=0 to colCount-1
        row=row &"    "& objRecordSet.fields(i)
    Next  
   
    Print row
    objRecordSet.moveNext
Wend
Set objRecordSet = Nothing
objCon.Close
Set objCon = Nothing

Screen-shots of sample excel and output are following -

In case of any queries, please post your comments.