Binding ComboBox to data from Excel

[edolf8]

Hi Everyone,

I have bound data from Excel to my project, but now I am trying to pull a specific column from the table and put the information in a combobox. I am trying to pull dates from column O of my excel file, and I want the dates to go into the combobox with no repeats. Does anyone have any suggestions?

Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='" & strFileName & "';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim cmd As OleDbCommand = New OleDbCommand( _
            "SELECT * FROM [Sheet1$O]", MyConnection)
            'Throws an error that [Sheet1$O] is not valid
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable

            While dr.Read()
                dateComboBox.Items.Add(dr.Item("*").ToString)
            End While

        End Using

[.paul.]

try this:

Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='" & strFileName & "';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
'moApp.Visible = True
 
Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
    MyConnection.Open()
 
    Dim da As New OleDbDataAdapter( _
    "SELECT * FROM [Sheet1$O]", MyConnection)
    
    Dim dt As New DataTable
    da.Fill(dt)
 
    dateComboBox.displaymember = dt.Columns(0).ColumnName
    dateComboBox.datasource = dt
        
End Using

[edolf8]

Hi Paul,

Your code worked for me! Thanks so much! Now I was wondering if you could help me with another problem. I am pulling different information from the same file later, and I need to use the combobox as a filter for the other information. I already have a filter in place, and I was wondering if I could add to that filter or if I have to make another filter? Let me know if any of this was confusing!


This is the code for binding the combobox to the information

        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source='" & strFileName & "';" & _
            "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim da As New OleDbDataAdapter( _
            "SELECT DISTINCT * FROM [Sheet1$O5:O]", MyConnection)

            Dim dt As New DataTable
            da.Fill(dt)

            dateComboBox.DisplayMember = dt.Columns(0).ColumnName
            dateComboBox.DataSource = dt
            ediDate = dateComboBox.SelectedItem.ToString()
        End Using

Here is the code for the 2nd information pull where I need the filter

Private Sub dataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataButton.Click
        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
    "data source='" & strFileName & "';" & _
    "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""
        'moApp.Visible = True

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()

            Dim cmd As OleDbCommand = New OleDbCommand( _
            "SELECT * FROM [Sheet1$J4:O]", MyConnection)
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            DataGridView1.DataSource = dt

            Me.BindingSource1.DataSource = dt
            Me.BindingSource1.Filter = String.Format("F1 = '76812SNE      
                A010M2'".ToString)
            Chart1.DataSource = BindingSource1

            Chart1.Series("Series1").XValueMember = "F3"
            Chart1.Series("Series1").YValueMembers = "F4"

            Chart1.DataBind()
        End Using
    End Sub

Thanks for your help!