By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,032 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,032 IT Pros & Developers. It's quick & easy.

reading excel file

P: n/a
Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change the
'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank
Nov 21 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Im not sure of the exact syntax, but cant you do something like
[Worksheets[0]]

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change the
'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank

Nov 21 '05 #2

P: n/a
if I specify 'worksheet' then I know the name, don't I?

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:#t**************@tk2msftngp13.phx.gbl...
Im not sure of the exact syntax, but cant you do something like
[Worksheets[0]]

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
Hi,
I use
Dim cnn As New

OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change the 'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank


Nov 21 '05 #3

P: n/a
My point was to address the worksheet by index rather than by name. You said
you wanted to avoid using the name right ? I mean you do have to direct the
select statment to an entity for it to work with.

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Use the following to email me

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Debug.WriteLine(ob("ufssz/cvsotAhsfbuTpmvujpotXjui/OFU", False))

End Sub

Private Function ob(ByVal email As String, ByVal inc As Boolean) As
String

Dim ch() As Char
Dim i As Int32
Dim stepValue As Int16

If inc Then stepValue = 1 Else stepValue = -1

ch = email.ToCharArray()

For i = 0 To ch.Length - 1
ch(i) = Convert.ToChar(Convert.ToInt16(ch(i)) + stepValue)
Next

Return New String(ch)

End Function
Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news3.zwoll1.ov.home.nl...
if I specify 'worksheet' then I know the name, don't I?

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message news:#t**************@tk2msftngp13.phx.gbl...
Im not sure of the exact syntax, but cant you do something like
[Worksheets[0]]

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change
the 'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank



Nov 21 '05 #4

P: n/a
I think there may be some confusion here. The Reference Terry was using is
when you're dealing with a WorkBook object in the Com interop library - that
won't work here - I'm 99% sure that the .Name property of the worksheet is
used in the derivation - not 100% sure but I'd bet $50.00 on it.

You can use the GetOleDbSchemaTable method to get a name of the tables (ie
sheets) in the workbook using it and since it comes back as a datatable,
you can use Numeric indices for iteration without knowing the names - and
from thatt you can find out what the names are. To do it with Excel
specifically -
http://support.microsoft.com/default...b;EN-US;318373

For GetOleDbSchemaTable example in general
http://www.knowdotnet.com/articles/g...hematable.html

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change the
'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank

Nov 21 '05 #5

P: n/a
Terry: I don't think he's using the Interop library - in which case you
have an excellent point. However here I think he needs to go with the
GetOleDbSchemaTable method because a big reason for using ADO.NET to query
the sheet is to avoid Interop in the first place. It's an easy mistake to
make, have done it myself on occassion.
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:ON**************@TK2MSFTNGP15.phx.gbl...
My point was to address the worksheet by index rather than by name. You said you wanted to avoid using the name right ? I mean you do have to direct the select statment to an entity for it to work with.

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Use the following to email me

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

Debug.WriteLine(ob("ufssz/cvsotAhsfbuTpmvujpotXjui/OFU", False))

End Sub

Private Function ob(ByVal email As String, ByVal inc As Boolean) As
String

Dim ch() As Char
Dim i As Int32
Dim stepValue As Int16

If inc Then stepValue = 1 Else stepValue = -1

ch = email.ToCharArray()

For i = 0 To ch.Length - 1
ch(i) = Convert.ToChar(Convert.ToInt16(ch(i)) + stepValue)
Next

Return New String(ch)

End Function
Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news3.zwoll1.ov.home.nl...
if I specify 'worksheet' then I know the name, don't I?

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:#t**************@tk2msftngp13.phx.gbl...
Im not sure of the exact syntax, but cant you do something like
[Worksheets[0]]

--

OHM ( Terry Burns )
. . . One-Handed-Man . . .
If U Need My Email ,Ask Me

Time flies when you don't know what you're doing

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
> Hi,
> I use
> Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
> Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
> Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
> Dim ds As New DataSet("TestExcel")
>
> da.Fill(ds)
>
> to read an excel spreadsheet. Works fine. But is it possible to

change
the
> 'select' statement so I don't have to know the sheetname?
> Thanks in advance
> Frank
>
>



Nov 21 '05 #6

P: n/a
GetOleDbSchemaTable works fine, thank you for the prompt reaction.
Frank

"W.G. Ryan eMVP" <Wi*********@NoSpam.gmail.com> wrote in message
news:OB**************@TK2MSFTNGP10.phx.gbl...
I think there may be some confusion here. The Reference Terry was using is when you're dealing with a WorkBook object in the Com interop library - that won't work here - I'm 99% sure that the .Name property of the worksheet is
used in the derivation - not 100% sure but I'd bet $50.00 on it.

You can use the GetOleDbSchemaTable method to get a name of the tables (ie
sheets) in the workbook using it and since it comes back as a datatable,
you can use Numeric indices for iteration without knowing the names - and
from thatt you can find out what the names are. To do it with Excel
specifically -
http://support.microsoft.com/default...b;EN-US;318373

For GetOleDbSchemaTable example in general
http://www.knowdotnet.com/articles/g...hematable.html

"Frank" <fr***@frank.com> wrote in message
news:cj**********@news1.zwoll1.ov.home.nl...
Hi,
I use
Dim cnn As New

OleDb.OleDbConnection("Provider=Microsoft.Jet.OLED B.4.0;Data
Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;""")
Dim da As New OleDb.OleDbDataAdapter("Select * from [Sheet1$]", cnn)
Dim ds As New DataSet("TestExcel")

da.Fill(ds)

to read an excel spreadsheet. Works fine. But is it possible to change the 'select' statement so I don't have to know the sheetname?
Thanks in advance
Frank


Nov 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.