?
Solved

ado and excel

Posted on 2003-10-23
10
Medium Priority
?
373 Views
Last Modified: 2010-05-01
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
0
Comment
Question by:RonaldBiemans
10 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 9606174
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
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 9606192
I don't get an error. I just doesn't put a value in there
0
 
LVL 3

Expert Comment

by:TheQuestion
ID: 9606244
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 43

Expert Comment

by:TimCottee
ID: 9606340
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?
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 9606420
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.

0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 9606700
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?
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 1000 total points
ID: 9606727
Not without having a look at the spreadsheet itself.
0
 
LVL 9

Expert Comment

by:Dang123
ID: 9608391
What package is producing the excel file?
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 9628123
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)
0
 
LVL 25

Author Comment

by:RonaldBiemans
ID: 9628127
Thanks for your time though

RB
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question