PC Review


Reply
Thread Tools Rate Thread

Problem Reading Excel Spreadsheet From VB.NET

 
 
Michael C#
Guest
Posts: n/a
 
      26th Jan 2005
Hi all,

I have a little program that uses OleDb to open and read an Excel
spreadsheet from VB.NET. The problem I'm running into is it's not reading
the column headers... The Excel worksheet looks has the following columns:

Data Description / Source / 1990 / 1991 / 1992 / etc.

It's set up as a pivot-table (at least I think so... not too familiar with
Excel Pivot Tables), based on the first two columns. Anyway, when I run the
application I can open and read the first two column headings, but the
others all come through as "F3", "F4", etc. Anyone have any ideas? Here's
my code:

Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
objConn.Open()
Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataset1)
Dim Arr As New ArrayList
For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
Dim s As String = c.ColumnName
Arr.Add(s)
Next
objAdapter1.Dispose()
objCmdSelect.Dispose()
objConn.Close()
objConn.Dispose()
TIA


 
Reply With Quote
 
 
 
 
Michael C#
Guest
Posts: n/a
 
      26th Jan 2005
Oops. Posted to the wrong place, let me try again. Please excuse
double-post:

I changed the name of one of the columns to "X" and it read the ColumnName
correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force it
to recognize these values as actual ColumnNames so I don't lose the
information?

Thanks


"Michael C#" <(E-Mail Removed)> wrote in message
news:ljDJd.1190$(E-Mail Removed)...
> Hi all,
>
> I have a little program that uses OleDb to open and read an Excel
> spreadsheet from VB.NET. The problem I'm running into is it's not reading
> the column headers... The Excel worksheet looks has the following
> columns:
>
> Data Description / Source / 1990 / 1991 / 1992 / etc.
>
> It's set up as a pivot-table (at least I think so... not too familiar with
> Excel Pivot Tables), based on the first two columns. Anyway, when I run
> the application I can open and read the first two column headings, but the
> others all come through as "F3", "F4", etc. Anyone have any ideas?
> Here's my code:
>
> Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
> objConn.Open()
> Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
> Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
> Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
> objAdapter1.SelectCommand = objCmdSelect
> objAdapter1.Fill(objDataset1)
> Dim Arr As New ArrayList
> For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
> Dim s As String = c.ColumnName
> Arr.Add(s)
> Next
> objAdapter1.Dispose()
> objCmdSelect.Dispose()
> objConn.Close()
> objConn.Dispose()
> TIA
>
>



 
Reply With Quote
 
 
 
 
Michael C#
Guest
Posts: n/a
 
      26th Jan 2005
I think I found the solution. Setting HDR=YES in the Extended Properties of
the OleDb Connection string seems to do the trick.

Thanks

"Michael C#" <(E-Mail Removed)> wrote in message
news:RHDJd.1192$(E-Mail Removed)...
> Oops. Posted to the wrong place, let me try again. Please excuse
> double-post:
>
> I changed the name of one of the columns to "X" and it read the ColumnName
> correctly. I guess it's assigning alphanumeric "F3, F4, etc." because the
> ColumnNames are all numeric (1990, 1991, etc.) Is there any way to force
> it
> to recognize these values as actual ColumnNames so I don't lose the
> information?
>
> Thanks
>
>
> "Michael C#" <(E-Mail Removed)> wrote in message
> news:ljDJd.1190$(E-Mail Removed)...
>> Hi all,
>>
>> I have a little program that uses OleDb to open and read an Excel
>> spreadsheet from VB.NET. The problem I'm running into is it's not
>> reading the column headers... The Excel worksheet looks has the
>> following columns:
>>
>> Data Description / Source / 1990 / 1991 / 1992 / etc.
>>
>> It's set up as a pivot-table (at least I think so... not too familiar
>> with Excel Pivot Tables), based on the first two columns. Anyway, when I
>> run the application I can open and read the first two column headings,
>> but the others all come through as "F3", "F4", etc. Anyone have any
>> ideas? Here's my code:
>>
>> Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
>> objConn.Open()
>> Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
>> Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
>> Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
>> objAdapter1.SelectCommand = objCmdSelect
>> objAdapter1.Fill(objDataset1)
>> Dim Arr As New ArrayList
>> For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
>> Dim s As String = c.ColumnName
>> Arr.Add(s)
>> Next
>> objAdapter1.Dispose()
>> objCmdSelect.Dispose()
>> objConn.Close()
>> objConn.Dispose()
>> TIA
>>
>>

>
>



 
Reply With Quote
 
Al Jones
Guest
Posts: n/a
 
      26th Jan 2005
Also, just a shot in the dark, I'd bet that if you changed them from the
default of numeric to character they'd come up correct, too.

On Tue, 25 Jan 2005 22:32:24 -0500, Michael C# <(E-Mail Removed)> wrote:

> I think I found the solution. Setting HDR=YES in the Extended
> Properties of
> the OleDb Connection string seems to do the trick.
>
> Thanks
>
> "Michael C#" <(E-Mail Removed)> wrote in message
> news:RHDJd.1192$(E-Mail Removed)...
>> Oops. Posted to the wrong place, let me try again. Please excuse
>> double-post:
>>
>> I changed the name of one of the columns to "X" and it read the
>> ColumnName
>> correctly. I guess it's assigning alphanumeric "F3, F4, etc." because
>> the
>> ColumnNames are all numeric (1990, 1991, etc.) Is there any way to
>> force
>> it
>> to recognize these values as actual ColumnNames so I don't lose the
>> information?
>>
>> Thanks
>>
>>
>> "Michael C#" <(E-Mail Removed)> wrote in message
>> news:ljDJd.1190$(E-Mail Removed)...
>>> Hi all,
>>>
>>> I have a little program that uses OleDb to open and read an Excel
>>> spreadsheet from VB.NET. The problem I'm running into is it's not
>>> reading the column headers... The Excel worksheet looks has the
>>> following columns:
>>>
>>> Data Description / Source / 1990 / 1991 / 1992 / etc.
>>>
>>> It's set up as a pivot-table (at least I think so... not too familiar
>>> with Excel Pivot Tables), based on the first two columns. Anyway,
>>> when I
>>> run the application I can open and read the first two column headings,
>>> but the others all come through as "F3", "F4", etc. Anyone have any
>>> ideas? Here's my code:
>>>
>>> Dim objConn As OleDbConnection = New OleDbConnection(sConnectionString)
>>> objConn.Open()
>>> Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
>>> Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
>>> Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
>>> objAdapter1.SelectCommand = objCmdSelect
>>> objAdapter1.Fill(objDataset1)
>>> Dim Arr As New ArrayList
>>> For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
>>> Dim s As String = c.ColumnName
>>> Arr.Add(s)
>>> Next
>>> objAdapter1.Dispose()
>>> objCmdSelect.Dispose()
>>> objConn.Close()
>>> objConn.Dispose()
>>> TIA
>>>
>>>

>>
>>

>
>




--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
 
Reply With Quote
 
Michael C
Guest
Posts: n/a
 
      26th Jan 2005
Actually I tried that and it didn't work with HDR=YES. I'm assuming that
OleDb probably won't let you use all-numeric names for columns? Not sure,
but that's what it ended up looking like. I ended up setting the extended
properties to HDR=NO and treating the first line as just another row of
data. Also had to set IMEX=1 in the extended properties to get consistent
results.

Thanks,
Michael C#

"Al Jones" <(E-Mail Removed)> wrote in message
newspsk6623o1xhg4go@aljones...
> Also, just a shot in the dark, I'd bet that if you changed them from the
> default of numeric to character they'd come up correct, too.
>
> On Tue, 25 Jan 2005 22:32:24 -0500, Michael C# <(E-Mail Removed)> wrote:
>
> > I think I found the solution. Setting HDR=YES in the Extended
> > Properties of
> > the OleDb Connection string seems to do the trick.
> >
> > Thanks
> >
> > "Michael C#" <(E-Mail Removed)> wrote in message
> > news:RHDJd.1192$(E-Mail Removed)...
> >> Oops. Posted to the wrong place, let me try again. Please excuse
> >> double-post:
> >>
> >> I changed the name of one of the columns to "X" and it read the
> >> ColumnName
> >> correctly. I guess it's assigning alphanumeric "F3, F4, etc." because
> >> the
> >> ColumnNames are all numeric (1990, 1991, etc.) Is there any way to
> >> force
> >> it
> >> to recognize these values as actual ColumnNames so I don't lose the
> >> information?
> >>
> >> Thanks
> >>
> >>
> >> "Michael C#" <(E-Mail Removed)> wrote in message
> >> news:ljDJd.1190$(E-Mail Removed)...
> >>> Hi all,
> >>>
> >>> I have a little program that uses OleDb to open and read an Excel
> >>> spreadsheet from VB.NET. The problem I'm running into is it's not
> >>> reading the column headers... The Excel worksheet looks has the
> >>> following columns:
> >>>
> >>> Data Description / Source / 1990 / 1991 / 1992 / etc.
> >>>
> >>> It's set up as a pivot-table (at least I think so... not too familiar
> >>> with Excel Pivot Tables), based on the first two columns. Anyway,
> >>> when I
> >>> run the application I can open and read the first two column headings,
> >>> but the others all come through as "F3", "F4", etc. Anyone have any
> >>> ideas? Here's my code:
> >>>
> >>> Dim objConn As OleDbConnection = New

OleDbConnection(sConnectionString)
> >>> objConn.Open()
> >>> Dim strCmd As String = String.Format("SELECT * FROM [{0}]", table)
> >>> Dim objCmdSelect As OleDbCommand = New OleDbCommand(strCmd, objConn)
> >>> Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter
> >>> objAdapter1.SelectCommand = objCmdSelect
> >>> objAdapter1.Fill(objDataset1)
> >>> Dim Arr As New ArrayList
> >>> For Each c As DataColumn In ds.Tables(0).Rows(0).ItemArray
> >>> Dim s As String = c.ColumnName
> >>> Arr.Add(s)
> >>> Next
> >>> objAdapter1.Dispose()
> >>> objCmdSelect.Dispose()
> >>> objConn.Close()
> >>> objConn.Dispose()
> >>> TIA
> >>>
> >>>
> >>
> >>

> >
> >

>
>
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem reading value from Excel spreadsheet Jack Microsoft Excel Programming 3 12th Oct 2006 02:42 AM
Import excel spreadsheet into an excel spreadsheet =?Utf-8?B?a2ZsZXRjaGI=?= Microsoft Excel Worksheet Functions 2 4th Aug 2006 12:49 AM
Copy Records from one excel spreadsheet to another excel spreadsheet chris_quinn26@yahoo.com Microsoft Access External Data 1 26th Jun 2006 09:20 PM
Program link to Excel spreadsheet opens Excel but not spreadsheet =?Utf-8?B?Y2I=?= Microsoft Excel Crashes 1 11th Feb 2006 03:55 PM
Macro to save excel XP spreadsheet to excel 97 spreadsheet format Mas Microsoft Excel Programming 1 11th Feb 2004 09:44 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:45 PM.