ado and excel

I'm trying to read data from an excel file into an ado recordset.
The problem is it doesn't read beyond column 8.
My sheet has 10 collumns filled with data, when I count the columns (nCols=oRS.Fields.Count)
it says there are 10 columns when I list the field names (oRS.Fields(i).Name) it gives
me 10 names, but when I read the data (oRS.Fields(i).Value) it only reports data in columns 1 to 8. What am I doing wrong.


    Dim oConn As ADODB.Connection
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & strExcelFilename & ";" & _
               "Extended Properties=""Excel 8.0;HDR=YES;"""
    Dim sTableName As String
    sTableName = "[" & cbSheets.List(cbSheets.ListIndex) & "$]"
    Dim oRS As ADODB.Recordset, nCols As Integer
    Set oRS = New ADODB.Recordset
    oRS.Open sTableName, oConn, adOpenStatic, adLockOptimistic
    nCols = oRS.Fields.Count
    Dim i As Integer, sFields As String, sData As String
    For i = 0 To nCols - 1
        sFields = sFields & oRS.Fields(i).Name & vbTab
    Debug.Print sFields
    Do While Not oRS.EOF
        sData = ""
        For i = 0 To nCols - 1
            sData = sData & oRS.Fields(i).Value & vbTab
        Debug.Print sData
LVL 25
Who is Participating?
TimCotteeConnect With a Mentor Commented:
Not without having a look at the spreadsheet itself.
Hi RonaldBiemans,

What error do you get? Or does the code execute and just not return the extra data?

Brainbench MVP for Visual Basic
RonaldBiemansAuthor Commented:
I don't get an error. I just doesn't put a value in there
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

go from o to count and if not mistaken the first line of the excel SS is the name of the column so i would add another to the loop
Of course there is data there in the first place, is there anything special about the data in these extra columns or are they simply numbers/text? Is there any possibility that you are getting NULL values back?
RonaldBiemansAuthor Commented:
Yes there is data in there. But there is definitly some strange with the excel file I use
because I create one from scratch and then it reads all the columns just fine.
The excel file I used is produced by some third party software.
But I can't seem to find anything wrong with those last 2 columns.

RonaldBiemansAuthor Commented:
This is freaking me out, when I insert columns before and after those 2 columns, these are read just fine. But it will not read those 2 original columns not even with copy , paste special/values.
Does anybody know what can be different about those 2 columns?
What package is producing the excel file?
RonaldBiemansAuthor Commented:
I could not figure out what was wrong with the excel file so I did it the old fashion way (automation and reading cell by cell)
RonaldBiemansAuthor Commented:
Thanks for your time though

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.