[RESOLVED] OleDB/Excel Sheet Import

[Besoup]

Hey all,
I currently have some code to import a spread into a dataset but it is dependant on the name of the sheet ie sheet1$. I would like this import to work on the first sheet of a xls file no matter what the sheet name is. Any ideas?

Private Function GetAllRows(ByRef objCon As OleDb.OleDbConnection) As DataSet
        Dim results As New DataSet("ExcelRows")
        Dim com As New OleDb.OleDbCommand("select * from [sheet1$]", objCon)
        Dim da As New OleDb.OleDbDataAdapter()
        da.SelectCommand = com
        da.Fill(results)
        Return results
    End Function

[stavris]

Hi,

Have you tried getting the worksheets from the excel workbook object? If you know the name of the excel file you can get the workbook object and then from there all the sheets that are included in the workbook and use the first one in your case.

Cheers,

--Stav.

[Besoup]

Thanks,
I actually did get it working via the Excel Object but I think I would like to stick to OleDB if it is possible.

[Besoup]

Got it!

Public Function GetSheetName(ByVal fileName As String) As String
        Dim objConn As OleDb.OleDbConnection
        Dim dt As System.Data.DataTable = Nothing
 
        Try
            objConn = Connect()
            dt = objConn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)
            Close(objConn)
            Return dt.Rows(0).Item("TABLE_NAME").ToString
        Catch ex As Exception
            Return Nothing
        Finally
            ' Clean up.
            If Not dt Is Nothing Then
                dt.Dispose()
            End If
        End Try
 
    End Function