How to get worksheet names from an excel file?

  • Thread starter Thread starter Terry Olsen
  • Start date Start date
T

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?
 
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.
 
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.
 
Here's the code i'm using. I got it from a C# example at:
http://weblogs.asp.net/donxml/archive/2003/08/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()
 
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?
 
Back
Top