ADO Usage in Excel

'Treat Excel like Database and retrieve data from it.


On Error Resume Next

'Const adOpenStatic = 3
'Const adLockOptimistic = 3
'Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
'Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\A032231\Desktop\Book2.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"

'objRecordset.Open "Select * FROM [Sheet1$]", _
' objConnection, adOpenStatic, adLockOptimistic, adCmdText

'strSearchCriteria = "Name = 'atl-fs-01'"
'objRecordSet.Find strSearchCriteria

'objRecordset.Close
'objConnection.Close


sql="Select * FROM [Sheet1$]"

set rs=objConnection.execute(sql)

i=0
'Get the row count from the values retrieved from the database
iRowCount = 0
Do While Not rs.EOF
iRowCount = iRowCount + 1
rs.MoveNext
Loop
If iRowCount = 0 then
MsgBox "No rows found"
else
fldCnt= rs.fields.count
rs.MoveFirst
Do While Not rs.EOF
For j= 0 To fldCnt-1
GData(j) = trim(rs.Fields(j).Value)
MsgBox GData(j)
Next
'Navigating to the next row in the database result set.
rs.MoveNext
Loop

'Close the Database Connection

objConnection.close

End If