reading excel file

[Frank]

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

[One Handed Man \( OHM - Terry Burns \)]

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

[Frank]

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

[One Handed Man \( OHM - Terry Burns \)]

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

[W.G. Ryan eMVP]

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

[W.G. Ryan eMVP]

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

[Frank]

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