Missing Excel Data

lankylad · Joined: 04 Oct 2007 Posts: 2

I am reading Excel Data into a Dataset in VB.net using the following code:

Dim MyDataset as New Dataset()
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\temp\tariff.xls; Extended Properties='Excel 8.0;HDR=YES'"

Dim mySingleData As New OleDbDataAdapter("SELECT rate1ceiling FROM
[tariff$] ", strConn)

mySingleData.Fill(myDataset)

Some of the rows in tariff.xls contain integer data, but once loaded into
myDataSet they are NULL. The same happens with "Select *", in that the same
column is not filled, but the rest are. The spreadsheet has 12555 rows and 20
columns. If I delete all but 5 rows then the problem disappears, so it seems
to be associated with size.

Archived from group: microsoft>public>vb>ole>automation

Paul Clement · Joined: 04 Oct 2007 Posts: 1560

On Fri, 29 Dec 2006 09:55:00 -0800, lankylad wrote:

¤ I am reading Excel Data into a Dataset in VB.net using the following code:
¤
¤ Dim MyDataset as New Dataset()
¤ Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
¤ Source=c:\temp\tariff.xls; Extended Properties='Excel 8.0;HDR=YES'"
¤
¤ Dim mySingleData As New OleDbDataAdapter("SELECT rate1ceiling FROM
¤ [tariff$] ", strConn)
¤
¤ mySingleData.Fill(myDataset)
¤
¤
¤ Some of the rows in tariff.xls contain integer data, but once loaded into
¤ myDataSet they are NULL. The same happens with "Select *", in that the same
¤ column is not filled, but the rest are. The spreadsheet has 12555 rows and 20
¤ columns. If I delete all but 5 rows then the problem disappears, so it seems
¤ to be associated with size.

Add the argument IMEX=1 to the Extended Properties parameter of your connection string.

Paul
~~~~
Microsoft MVP (Visual Basic)

lankylad · Joined: 04 Oct 2007 Posts: 2

Fixed!

Thanks for your help

"Paul Clement" wrote:

> On Fri, 29 Dec 2006 09:55:00 -0800, lankylad wrote:
>
> Â¤ I am reading Excel Data into a Dataset in VB.net using the following code:
> Â¤
> Â¤ Dim MyDataset as New Dataset()
> Â¤ Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Â¤ Source=c:\temp\tariff.xls; Extended Properties='Excel 8.0;HDR=YES'"
> Â¤
> Â¤ Dim mySingleData As New OleDbDataAdapter("SELECT rate1ceiling FROM
> Â¤ [tariff$] ", strConn)
> Â¤
> Â¤ mySingleData.Fill(myDataset)
> Â¤
> Â¤
> Â¤ Some of the rows in tariff.xls contain integer data, but once loaded into
> Â¤ myDataSet they are NULL. The same happens with "Select *", in that the same
> Â¤ column is not filled, but the rest are. The spreadsheet has 12555 rows and 20
> Â¤ columns. If I delete all but 5 rows then the problem disappears, so it seems
> Â¤ to be associated with size.
>
> Add the argument IMEX=1 to the Extended Properties parameter of your connection string.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)
>

COMDLG32 in Excel going missing... We have an Excel macro that calls Microsoft Common Dialog Control to display a "Save As." dialog. This works on only some of our clients machines. We've added the codebase attribute and if the control is not present on the client machine the user is promp

Get Excel Data All, Using VB 6.0, I need to get the data out of an Excel sheet and insert it in an existing Access table. I'd like to do this strictly through VB rather than start an instance of Access and use the function. Any pointers? Thanks,

transfer data between Excel and IE I would like a VB macro to retrieve a number from a spreadsheet cell, enter it into an IE browser field, submit the info ( there is a button on the browser that uses the below java code ), then read a line of text that is returned on the next browser page

Exporting data to Excel from VB6 I am a novice VB6 programmer and I need to export data aquired from an external piece of equipment to excel to allow the data to be manipulated. The data is displayed on the screen at present and I have no idea - can anyone help please. Thanks, Paul...

Insert Excel Data in Word document? Hi, I have a Word document with, among other texts, some tags. To each tag is associated a cell in an Excel sheet.(I might as well use the Excel cell naming convention as tags, ex : B3, H17, etc..) I would like to write a visual basic app to do the follow