Here is the function I use a lot to get sheet names, this specific example i know I always want the first sheets name, but you can enumerate through all the sheets. I am sure I stole borrowed  this from somewhere and adapted it to fit my need, but it works great as I pulled this out of an import screen that I know gets beat on quite a lot. Hope it helps.

''' <summary>

''' This method retrieves the excel sheet names from

''' an excel workbook.

''' </summary>

''' <param name="excelFile">The excel file.</param>

''' <returns>String[]</returns>

Private Function GetExcelSheetNames(ByVal excelFile As String) As String

Dim objConn As OleDbConnection = Nothing

Dim dt As System.Data.DataTable = Nothing

Try

' Connection String. Change the excel file to the file you

' will search.

Dim connString As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=") + excelFile & ";Extended Properties=Excel 8.0;"

' Create connection object by using the preceding connection string.

objConn = New OleDbConnection(connString)

' Open connection with the database.

objConn.Open()

' Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

If dt Is Nothing Then

Return Nothing

End If

Dim excelSheets As String() = New String(dt.Rows.Count - 1) {}

Dim i As Integer = 0

' Add the sheet name to the string array.

For Each row As DataRow In dt.Rows

excelSheets(i) = row("TABLE_NAME").ToString()

i += 1

Next

' Loop through all of the sheets if you want too...

For j As Integer = 0 To excelSheets.Length - 1

' Query each excel sheet.

Next

' Return excelSheets

Return excelSheets(0)

Catch ex As Exception

Return Nothing

Finally

' Clean up.

If objConn IsNot Nothing Then

objConn.Close()

objConn.Dispose()

End If

If dt IsNot Nothing Then

dt.Dispose()

End If

End Try

End Function

Keith Chisarik

Hi Juan,

That function would call a form with a drop down that is bound to the table_name property of the schema table. Actually in the production code that I am using I toggle the visibility of a combobox on and off on the import form. I was trying to simply pull out the pieces that were relevant to give a simple example. If I have a little spare time today I will see if I can make a sample.

Paul

Thanks for the idea, I want to try and add a sample of this (or if you have a sample). I have a problem with this:

GetSheetNames

I assume you create a function, can you tell me how this must be?

Regards

Everything is possible, just keep trying...

Awesome example Paul!

Hi here is a quick rundown on importing excel to a business object use oledb jet provider,

To Import Excel Data to a strongly typed Business Object you first have to create a business object and map to the data source just like any other datasource. Here is a quicky on how to do it.

1) Create and set up your business object project to use oledb and use the connection string as follows. You can then map and build your strong typed business object. You can set whether or not your sheet has header etc in the connection string

2) Add an Access Data Source item using the same connection string

3) Fill your business object

Just like any other data source that we generate a business object for and using strong typing we need to know in advance abouth the fields datatypes etc.However sometimes you might want to import an excel sheet that you don't know about at design time.So you can simply use regular ADO and oledb to import to a datatable and then do with it as you want. Here is a really basic example.

Hope that helps and it all makes sense as I was kinda in a hurry and pulling snippets from my application.

Paul

Thanks for the contribution, Juan!

Hi Teddy

Looks promising, I will review this and if something can be done, I will do my best to add a sample for import.

Regards

Everything is possible, just keep trying...

Maybe http://filehelpers.com/ could solve your problems.

I haven't used it yet, but i think it also have a MS Excel storage to import/export from.

/Teddy

Thanks for the contribution Juan Carlos!



On my list is the need to import from excel to a BO. The method I've been thinking about using is have Excel export the sheet to a CSV file, then use an ODBC driver, via a data source to access that data (via a BO of course). I'm going to investigate how you did the export, because that might be the first step of getting the data out of a BO into Excel, which the users could then change and I'd upload it.



I'm not planning on use OLE automation because its slow, and because my users are using everything from Excel 2000 to 2007.

Hi Juan Carlos,

Thanks for the contribution, I ran your sample and it worked as advertised. Sorry I can not help you out in your need to import from Excel to a BO.

Edhy Rijo