Read Excel Spreadsheet Sheet Names

What seemed like a reasonably simple problem became difficult because it became apparent that an Excel spreadsheet can contain more than one sheet with the same name. The idea here is that you get the sheet names, and display them in a combo box or something similar. I had this situation in an app at GE Healthcare but actually ended up doing something slightly different. But for the generic situation this will work. Hope this code helps somebody….
 
Private Sub btnGetSourceFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetSourceFile.Click
        Dim fileChooser As OpenFileDialog = New OpenFileDialog()
        fileChooser.Filter = "Microsoft Excel (*.xls)|*.xls|All files (*.*)|*.*"
        Dim result As DialogResult = fileChooser.ShowDialog()
        If result = DialogResult.Cancel Then
            Return
        End If
        txtSourceFile.Text = fileChooser.FileName

        Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        xlApp = New Excel.Application()
        xlWB = xlApp.Workbooks.Open(txtSourceFile.Text)
        Dim i As Integer = 0
        For Each xlSheet In xlWB.Sheets
            cbSheetName.Items.Insert(i, xlSheet.Name)
            i += 1
        Next
        xlWB.Close()
        xlApp.Quit()
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbSheetName.SelectedIndexChanged
        lblStats.Text = "Database: " & iXcountRows("DataSource") & " Records" & vbCrLf & "Source: " & iXcountExcel(txtSourceFile.Text, cbSheetName.Text) & " Records"
    End Sub

    Function iXcountExcel(ByVal fileSource As String, ByVal sheetName As String)
        Dim dsExcel As New DataSet("dsExcel")
        Dim cnn As New OleDbConnection()
        Dim cmd As New OleDbCommand()
        Dim da As New OleDbDataAdapter()
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileSource & ";Extended Properties=""Excel 8.0;HDR=YES;"""
        cmd.CommandText = "SELECT * FROM [" & sheetName & "$]"
        cmd.Connection = cnn
        da.SelectCommand = cmd
        Return da.Fill(dsExcel)
    End Function

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: