How to get worksheet names from an excel file?

[Terry Olsen]

I want to get worksheet names from an Excel file using an
OleDbConnection. I tried using GetOleDbSchemaTable but it returns zero
rows.

How can I best get worksheet names from an Excel file?
*** Sent via Developersdex http://www.developersdex.com ***

[Scott M.]

I think you'd need to be using the Excel Object Model via the Office Primary
InterOp Assemblies and then you can access the worksheets collection and
loop through them asking for each's name.
"Terry Olsen" <to******@hotmail.com> wrote in message
news:u2**************@TK2MSFTNGP09.phx.gbl...

I want to get worksheet names from an Excel file using an
OleDbConnection. I tried using GetOleDbSchemaTable but it returns zero
rows.

How can I best get worksheet names from an Excel file?
*** Sent via Developersdex http://www.developersdex.com ***

[Terry Olsen]

Would I have to distribute other files with the application using the
interop (such as excel.exe)? I'm trying to keep this a simple
stand-alone program. The target computers do not have Office installed.

*** Sent via Developersdex http://www.developersdex.com ***

[Terry Olsen]

Here's the code i'm using. I got it from a C# example at:
http://weblogs.asp.net/donxml/archiv.../21/24908.aspx

The Excel file i'm reading from has a single worksheet called "orders".

Whenever I run the code below, it returns zero rows in the Schema Table.

I need help! Thanks.

Dim ExcelConnectionStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
ExcelFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

Dim ExcelConnection As New _
OleDbConnection(ExcelConnectionStr)
Dim ExcelCommand = New OleDbCommand
ExcelCommand.connection = ExcelConnection
Dim ExcelAdapter As New OleDbDataAdapter(ExcelCommand)
ExcelConnection.Open()
Dim ExcelSheets As DataTable = _
ExcelConnection.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, _
New Object() {Nothing, Nothing, Nothing, "TABLE"})

MsgBox(ExcelSheets.Rows.Count)

For i As Integer = 0 To ExcelSheets.Rows.Count - 1
MsgBox(ExcelSheets.Rows(i).Item("TABLE_NAME"))
Next

ExcelConnection.Close()

*** Sent via Developersdex http://www.developersdex.com ***

[Terry Olsen]

I figured out what the problem may be. The Excel file in question is in
Excel 5.0/95 format.

I changed my connection string to show Excel 5.0 for Extended
Properties, but it still returns nothing in the SchemaTable. I tried
Excel 3.0 as well and still nothing.

I then saved a copy of the file in Excel 2003 format, and now the code
works great, it reads the worksheet name.

However, the file that the user will be using (downloading from a web
site) is in Excel 5.0 format. Is there any way to get this to work?

*** Sent via Developersdex http://www.developersdex.com ***