Solved

ado and excel

Posted on 2003-10-23
10
364 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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 250 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

821 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