trying to read a excel sheet

hi i am trying to read data frm 52 worksheets the code below doesnt
wor
any ideas


Dim rst As ADODB.Recordset
Dim oConn As ADODB.Connection
Set oConn = CreateObject("ADODB.Connection")
MsgBox App.Path & "\caretaker.xls"
strSQL = "SELECT * FROM ['29-Dec-2003$']"
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="
& App.Path & "\caretaker.xls;DefaultDir=" & App.Path

Set rst = oConn.Execute(strSQL)

Robert,

Check this code, it should do something similar to what you need:

Sub test()
Dim rst As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim sPath As String, sTmp As String
sPath = "E:\Doc\Test1.xls"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};Dbq=" & sPath & ";"
Set rst = oConn.Execute("SELECT * FROM [Sheet1$]")
Do While Not rst.EOF
sTmp = sTmp & rst.Fields(0) & vbTab & rst.Fields(1) & vbCrLf
Call rst.MoveNext
Loop
Debug.Print sTmp
Call rst.Close
Call oConn.Close
Set rst = Nothing
Set oConn = Nothing
End Sub

Hope this helps,

Max Kudrenko
Brainbench MVP Program for Visual Basic
<a rel="nofollow" style='text-decoration: none;' href="http://www.brainbench.com" target="_blank">www.brainbench.com</a>


merritt.robert RemoveThis @spsd.sk.ca (rob merritt) wrote in message ...
 > hi i am trying to read data frm 52 worksheets the code below doesnt
 > wor
 > any ideas
 >
 > Dim rst As ADODB.Recordset
 > Dim oConn As ADODB.Connection
 > Set oConn = CreateObject("ADODB.Connection")
 > MsgBox App.Path & "\caretaker.xls"
 > strSQL = "SELECT * FROM ['29-Dec-2003$']"
 > oConn.Open "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="
 > & App.Path & "\caretaker.xls;DefaultDir=" & App.Path
 > Set rst = oConn.Execute(strSQL)