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

How to get worksheet names from an excel file?

P: n/a
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 ***
Feb 9 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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 ***

Feb 9 '06 #2

P: n/a
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 ***
Feb 9 '06 #3

P: n/a
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 ***
Feb 9 '06 #4

P: n/a
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 ***
Feb 9 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.