Powerful SQL with Excel

Known for their integrity, VB scripts works great for any operations with MS-Excel spreadsheets.

Following simple funtion, can Query/Process the records from excel sheet. You can do most of the operations that you may want to do through table data just from a spreadsheet.

Assume, your excel file is C:\Book2.xls and the Work Sheet name is Sheet1


strConnectString ="Driver={Driver do Microsoft Excel(*.xls)};DBQ=C:\Book2.xls"
strSQL = "select * from [Sheet1$]"
Set objEnv = UDF_GetRecordset1(strConnectString,strSQL)
If Not objEnv.EOF Then
'msgBox('DataArray = objEnv.GetRows(objEnv.Recordcount)')
DataArray = objEnv.GetRows()
For row=0 To (objEnv.recordCount-1)
MsgBox "RowNum:"&row+1
For col=0 To (objEnv.Fields.count-1)
' MsgBox DataArray(col,row)
Next
Next
End If



Public Function UDF_GetRecordset1(strConnection,strSQL)
Dim objConn,objRec ' Variable declaration
Set UDF_GetRecordset1 = Nothing
Err.Clear
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConnection
If Err <> 0 Then
Set objConn = Nothing
Exit Function
End If
Set objRec = CreateObject("ADODB.Recordset")
objRec.CursorType = 1
objRec.Open strSQL,objConn
If Err <> 0 Then
strSQL
'MsgBox('step31')
Set objRec=Nothing
Set objConn = Nothing
Exit Function
End If
Set UDF_GetRecordset1 = objRec
Set objRec=Nothing
Set objConn=Nothing
End Function




This script can be tested with any VB Script editors- EditPlus.

Known issues:
Numeric field overflow
http://support.microsoft.com/kb/815277