To Query a Excel Sheet as a database

Share this post
FaceBook  Twitter  Mixx.mn     


To Query a Excel Sheet as database   , use following code:
Steps:

1) Create an ADODB connection  and recordset

2) Open connection

3) Create a Recordset

4) Execute SQL and store results in reocrdset

5) Read all fields data   and store in a array

6) Close and Discard all variables


Code:
'sFileName= xls file name with path
'Sheetname = table name
'VRstatmt = where statment for query
'Colname=Column name to fetch value from
'==============================================
Public Function QryXls_GetData( sFileName, SheetName, VRstatmt,Colname )
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = "&H0001"

sql_text="Select * FROM [" & SheetName & "$]" & VRstatmt
 '1) Create an ADODB connection and recordset
Set objConnection = CreateObject("ADODB.Connection")

'2) Open connection'
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sFileName & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;"";"

'3) Create a Recordset'
Set objRecordSet = CreateObject("ADODB.Recordset")

'4) Execute SQL and store results in reocrdset'
objRecordset.Open sql_text , objConnection, adOpenStatic, adLockOptimistic, adCmdText

'5) Read all fields data   and store in a array'
'For Multiple records
'=================
ReDim  SQLExpectedData(objRecordset.recordcount -1)
For i=0  to objRecordset.recordcount -1  'objRecordset.fields.item(1).properties.count
    SQLExpectedData(i)= objRecordset.fields(Colname)
    objRecordset.movenext
Next

'6) Close and Discard all variables '
objRecordset.Close
objConnection.Close
End Function