Solved

ado and excel

Posted on 2003-10-23
10
354 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now