Excel sheet names Question

[bdcubit@yahoo.com]

Hello all, I am a bit stuck. I am very new to VB. What I am trying to do is Read the sheet names from an Excel file and then populate a listbox with those names. However, I can not seem to figure out how to accomplish this. Any help would be greatfully appreciated.
Bruce

 

[grmbl]

First add a COM reference to your project.
(Project - Add Reference - COM - Microsoft Excel x.0 Object Library)

Code:

Imports excel = Microsoft.Office.Interop.Excel
Imports forms = System.Windows.Forms
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US")

        Dim xlapp As excel.Application
        Dim xlworkbook As excel.Workbook

        Dim dlg As New forms.OpenFileDialog

        With dlg
            .CheckFileExists = True
            .CheckPathExists = True
            .ShowDialog()
        End With

        If dlg.FileName <> Nothing Then

            xlapp = New excel.Application
            xlapp.Visible = False
            Try
                ' This gives me an error because Offices language is set to English
                ' but my regional settings are Dutch. I don't have the language packs
                ' installed. More information here: http://support.microsoft.com/kb/320369
                xlworkbook = New excel.Workbook

                'xlworkbook.GetType().InvokeMember("Open", Reflection.BindingFlags.InvokeMethod, Nothing, xlapp, Nothing, ci)
                xlworkbook = xlapp.Workbooks.Open(dlg.FileName)

                For Each w As excel.Worksheet In xlworkbook.Worksheets
                    ListBox1.Items.Add(w.Name)
                Next

                releaseObject(xlapp)
                releaseObject(xlworkbook)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

 

[bdcubit@yahoo.com]

More VB and Excel

Hello and thank you for your reply. I was able to work somewhat off from your thoughts and some Google information to retreive my Sheet list by using the following code.
-----------------------------------------------------------------
Dim ExcelConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & fileName & " ; " & "Extended Properties=Excel 8.0;")
ExcelConnection.Open()

Dim data As DataTable

data = ExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})


lstBox2.DisplayMember = "TABLE_NAME"
lstBox2.ValueMember = "TABLE_NAME"
lstBox2.DataSource = data

-----------------------------------------------
Now that I have recorded which Sheet the customer is working with, i would like to use this information to record data from the first row with an unknown number of columns. (It may be 5 or maybe 20. Its a varaible). I want to load this first (header) column into another listbox for the user to be able to select from that list. Any ideas?

 

[grmbl]

This perhaps? (please put future code between CODE tags.)

Dim sFile As String = "D:\Share\PRINTERS.xls"
        Dim sValues() As String = {}
        Dim sQuery As String = "SELECT * FROM [" & ComboBox1.Text & "A1:A50]"
        Dim cn As System.Data.OleDb.OleDbConnection
        Dim cmd As System.Data.OleDb.OleDbCommand
        Dim dr As System.Data.OleDb.OleDbDataReader
        cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source=" & sFile & " ; " & "Extended Properties=Excel 8.0;")
        cn.Open()
        cmd = New System.Data.OleDb.OleDbCommand(sQuery, cn)
        dr = cmd.ExecuteReader
        While dr.Read
            If Not dr.IsDBNull(0) Then
                ReDim Preserve sValues(sValues.GetUpperBound(0) + 1)
                sValues(sValues.GetUpperBound(0)) = dr(0)
            End If
        End While

        ComboBox2.Items.Clear()
        ComboBox2.Items.AddRange(sValues)

        cn.Close()