reading excel file

[Frank]

Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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]

if I specify 'worksheet' then I know the name, don't I?

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

Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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

if I specify 'worksheet' then I know the name, don't I?

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message

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

Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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

 

[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.aspx?scid=kb;EN-US;318373

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

 

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

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

if I specify 'worksheet' then I know the name, don't I?

"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message

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

Hi,
I use
Dim cnn As New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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

 

[Frank]

GetOleDbSchemaTable works fine, thank you for the prompt reaction.
Frank

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.aspx?scid=kb;EN-US;318373

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

Hi,
I use
Dim cnn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.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