ado and excel

RonaldBiemans
RonaldBiemans used Ask the Experts™
on
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.

Thanks



    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
    Next
    Debug.Print sFields
   
    Do While Not oRS.EOF
        sData = ""
        For i = 0 To nCols - 1
            sData = sData & oRS.Fields(i).Value & vbTab
        Next
        Debug.Print sData
        oRS.MoveNext
    Loop
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
TimCotteeHead of Software Services

Commented:
Hi RonaldBiemans,

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

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
Top Expert 2004

Author

Commented:
I don't get an error. I just doesn't put a value in there
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

TimCotteeHead of Software Services

Commented:
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?
Top Expert 2004

Author

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.

Top Expert 2004

Author

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?
Head of Software Services
Commented:
Not without having a look at the spreadsheet itself.

Commented:
What package is producing the excel file?
Top Expert 2004

Author

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)
Top Expert 2004

Author

Commented:
Thanks for your time though

RB

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial