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.
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.
Thanks!!
@Smiles
thnx for your inputs. code modified!
Really appreciate you for putting all the code up in one website. Thanks a lot..