ADO and Excel 2k[RESOLVED for now]

I am connecting to an Excel spreadsheet using ADO,
but the resulting recordset only has values for the text cells
and not any date or numeric cells. I don't see why the recordset
is not picking up the cells other than text.

Any ideas???

Code:

---

Dim sSQL As String
Dim oRsE As ADODB.Recordset
Dim oCnnE As New ADODB.Connection

oCnnE.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; DATA Source="
& frmMain.lblSVR_Path.Caption & ";Extended Properties=Excel 8.0;"
oCnnE.Open

sSQL = "SELECT"
sSQL = sSQL & " *"
sSQL = sSQL & " FROM"
sSQL = sSQL & " [Worksheet]"
   
Set oRsE = New ADODB.Recordset
oRsE.Open sSQL, oCnnE, adOpenStatic, adLockReadOnly, adCmdText

If IsDate(oRsE!F1) = True Then...

---

The F1 Field contains several rows of dates and they all come in as Null.

Thanks for any help.

I suspect this could be down to the 'adCmdText' argument, not sure about that one though. Here's a sample testing one I've just produced which works though, stating from range A1, copy this into a new spreadsheet & save it as "C:\book1.xls":

Code:

---

1        01/01/2003        a
2        02/01/2003        b
3        03/01/2003        c
4        04/01/2003        d
5        05/01/2003        e

---

Then in a new vb project, add a reference to the ADO object library and use this:

VB Code:

Private Sub Form_Load()
    Dim sSQL As String
    Dim oCnnE As ADODB.Connection
    Dim oRsE As ADODB.Recordset
    
    Set oCnnE = New ADODB.Connection
    
    oCnnE.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "DATA Source=C:\book1.xls;Extended Properties=Excel 8.0;"
    
    oCnnE.Open
    
        sSQL = "SELECT * from [Sheet1$]"
            
        Set oRsE = New ADODB.Recordset
            oRsE.Open sSQL, oCnnE, , , adCmdText
            
            Do Until oRsE.EOF
                Print oRsE.Fields(0) & "  " & oRsE.Fields(1) & _
                "  " & oRsE.Fields(2)
                oRsE.MoveNext
            Loop
        oRsE.Close
        Set oRsE = Nothing
    
    oCnnE.Close
    Set oCnnE = Nothing
End Sub

The tests work fine so it has to be something with the workbook.
The workbook is generated by another program. If I copy the worksheet
to another workbook it won't work either, but if I manually type some of the data
into another workbook then it works.

Oh well, I don't have all day for this so I will just use the Excel object instead. :(

What if you called the sheet & altered every cells format to be text, does this maybe copy over?

ActiveSheet.Cells.NumberFormat = "@"

So your saying to open the spreadsheet and format the cells
to all be text and then read it in?
Then it should be able to read in the values.
I will test it out this weekend and let you know.

Thanks.