VB.NET (Excel Two Columns > Conditional Comboboxes)

Question

I'm searching for nearly two hours to find a solution on the following. In Excel I have two columns (One Column for Master Records and one Column for Slave Records). Basically, in Combobox1 I want to populate all the Master Records. If a selection is made for MasterRecord A, I want Combobox2 to only show me the SlaveRecords belonging to A and not the other records belonging to other Master Records.

I have the Interop Assembly added and Excel opened (there is a connection already). Your help is much appreciated!

Private Sub Combobox2_Populate()
    'Start Excel Script to populate ComboBox2
    Dim excel As Application = New Application
    Dim w As Workbook = excel.Workbooks.Open(Filename:=databasestatus, [ReadOnly]:=True)
    Dim sheet As Worksheet = w.Sheets("AIR_NL_1")
    Dim StartRow As Integer
    Dim TotalRows As Integer
    ComboBox2.Items.Clear()
    sheet.UsedRange.AutoFilter(Field:=9, Criteria1:=ComboBox1.SelectedItem, Operator:=XlAutoFilterOperator.xlFilterValues)
    TotalRows = sheet.Range("A1").CurrentRegion.Rows.Count
    For StartRow = 3 To TotalRows
        If XlCellType.xlCellTypeVisible = True Then
            ComboBox2.Items.Add(sheet.Range("H:H").Cells(StartRow, 1).Text)
        End If
    Next
    w.Close(SaveChanges:=False)
End Sub    

Answer 1

This might help you, or at least give you a basic idea:

Private Function ExcelToDataTable(ByVal fileExcel As String, _ 
                                  Optional ByVal columnToExtract As String = "*", _
                                  ) As System.Data.DataTable
    Dim dt As New System.Data.DataTable
    Try
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim MyCommand As OleDbDataAdapter
        Dim fileExcelType As String

        'Chose the right provider
        If IO.Path.GetExtension(fileExcel.ToUpper) = ".XLS" Then
            fileExcelType = "Excel 8.0"
            MyConnection = _
            New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileExcel & "';Extended Properties=" & fileExcelType & ";")
        Else
            fileExcelType = "Excel 12.0"
            MyConnection = _ 
            New System.Data.OleDb.OleDbConnection _
            ("provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & fileExcel & "';Extended Properties=" & fileExcelType & ";")
        End If

        'Open excel connection
        MyConnection.Open()

        'Populate DataTable
        Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")
        MyCommand = New OleDbDataAdapter(String.Format("SELECT " & columnToExtract & " FROM [{0}] ", myTableName), MyConnection)
        MyCommand.TableMappings.Add("Table", columnToExtract)
        MyCommand.Fill(dt)
        MyConnection.Close()
    Catch ex As Exception
        Err.Clear()
    End Try
    Return dt
End Function

As you can see, we have an optional parameter called myWhereStatement.

What does it mean? You can specify its value when you call the function, otherwise its value will be an empty string

After that we can call ExcelToDataTable inside our Sub in order to populate the ComboBox as shown below:

Private Sub Combobox_Populate()
    Dim filePath As String = "your_file_path"
    ComboBox1.DataSource = ExcelToDataTable(filePath, "MasterRecord") 
End Sub

Now you have your ComboBox1 filled with data, but the ComboBox2 is still empty.

We are going to handle the event of ComboBox1_SelectedValueChanged that means that every time you select an Item from the ComboBox1 it will programmatically fill the ComboBox2 with the propper items as shown below.

Private Sub ComboBox1_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedValueChanged
    Dim slaveRecords As System.Data.DataTable = ExcelToDataTable(filePath)
    Dim dt As New DataTable
    dt.Columns.Add("SlaveRecords")
    For i As Integer = 0 To slaveRecords.Rows.Count
        If ComboBox1.SelectedItem Is slaveRecords.Rows(i).Item(0) Then
            dt.Rows.Add(slaveRecords.Rows(i).Item(1))
        End If
    Next
    ComboBox2.DataSource = dt
End Sub

Remarks

As you can see the first call of ExcelToDataTable has only 2 parameters while the second one has 3 parameters. That's the optional parameter feature!

N.B.

As you can see I'm using alot of _ because of better code formatting. It means that a single statement will continue across multiple lines

If something isn't 100% clear ot you have any dubt feel free to ask in the comments below.