Fill a ComboBox (vb.net) from an Excel Column

Question

Hello everyone!

I'm currently coding an Application Windows Form (.NET Framework) on Visual Studio 2017.

After having search almost everywhere, I still can't find an answer or a lead.

My program is a form containing multiple comboBox which will contain values taken from the Excel columns, and its purpose is to copy/past rows of the Excel File where the value of the columns equal the comboBox selected value.

And the problem is that I don't know how to fill the comboBox.

Any help would be great, if you need my code or Project file tell me.

Answer 1

While I won't discourage you from making an app for this, as you may have other purposes in mind as well, this sounds like something you could do with a VLOOKUP in Excel.

Anyway, this should be a fairly simple matter of selecting the range of cells within the worksheet and the looping through the values of each cell.

These links should be helpful:

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/walkthrough-office-programming (covers VB as well)

https://msdn.microsoft.com/en-us/library/office/dn301006.aspx

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

Answer 2

Hello,

I don't have an exact code sample that meets your needs but do have methods that can get you there but be forewarned a) it's not simple code so you would need to study the code b) main code is VB.NET but a small part of the code is in C# c) all code uses OleDb, other smart methods are using (if .xlsx) SpreadSheetLight free library where all code samples are in C# but I have samples in VB.NET here.

In the following MSDN code sample I show how to create a DataTable that is based off a range selected in a form. Note the SELECT statement which is built in code could very well be extended to include a WHERE condition.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 3

Hi Biright

Do you want this effect?

I hope the following code can help you.

Imports Excel1 = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
            ComboBox1.Items.Add(GetDataFromExcelByCom(False).Rows(i)(0))
        Next
        For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
            ComboBox2.Items.Add(GetDataFromExcelByCom(False).Rows(i)(1))
        Next
    End Sub
    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable
        'Dim openFile As OpenFileDialog = New OpenFileDialog()
        'openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls"
        'openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        'openFile.Multiselect = False
        'If openFile.ShowDialog() = DialogResult.Cancel Then Return Nothing
        'Dim excelFilePath = openFile.FileName
        Dim excelFilePath = "C:\Users\alexl2\Desktop\1.xlsx"
        Dim app As Excel1.Application = New Excel1.Application()
        Dim sheets As Excel1.Sheets
        Dim oMissiong As Object = System.Reflection.Missing.Value
        Dim workbook As Excel1.Workbook = Nothing
        Dim dt As DataTable = New DataTable()

        Try
            If app Is Nothing Then Return Nothing
            workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
            sheets = workbook.Worksheets
            'Dim worksheet As Excel1.Worksheet = CType(sheets.get_Item(1), Excel1.Worksheet)
            Dim worksheet As Excel1.Worksheet = sheets(1)
            Dim ji As Integer = CType(12, Integer)
            If worksheet Is Nothing Then Return Nothing
            Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
            Dim iColCount As Integer = worksheet.UsedRange.Columns.Count

            For i As Integer = 0 To iColCount - 1
                Dim name = "column" & i

                If hasTitle Then
                    Dim txt = (CType(worksheet.Cells(1, i + 1), Excel1.Range)).Text.ToString()
                    If Not String.IsNullOrWhiteSpace(txt) Then name = txt
                End If

                While dt.Columns.Contains(name)
                    name = name & "_1"
                End While

                dt.Columns.Add(New DataColumn(name, GetType(String)))
            Next

            Dim range As Excel1.Range
            Dim rowIdx As Integer = If(hasTitle, 2, 1)

            For iRow As Integer = rowIdx To iRowCount
                Dim dr As DataRow = dt.NewRow()

                For iCol As Integer = 1 To iColCount
                    range = CType(worksheet.Cells(iRow, iCol), Excel1.Range)
                    dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
                Next

                dt.Rows.Add(dr)
            Next

            Return dt
        Catch
            Return Nothing
        Finally
            workbook.Close(False, oMissiong, oMissiong)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            workbook = Nothing
            app.Workbooks.Close()
            app.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
            app = Nothing
        End Try
    End Function
End Class

Best RestRegards,

Alex

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Thanks for the reply Reed! I'll look into it!

Answer 5

Thx for the reply Karen! That's quite helpful, I'll see what I can learn.

Answer 6

Hi Alex! Thank you for your answer, that is indeed the effect I want, I'll try to see what I can do with it!

*Edit*

Forgot to say that my program got 2 form :

• 1 : Browse and open the excel File
• 2 : The ComboBox and the exportation button

Here's what I have so far with your code:

1st Form

Imports Microsoft.Office.Interop.Excel

Public Class Form1
    Public Property ExcelApp As Application
    Public Property ExcelWorkBook As Workbook

    Private Sub Browse1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Browse1.Click
        ofd.Filter = "Excel Files(.xls)|*.xls|Excel Files(.xlsx)|*.xlsx|Excel Files(*.xlsm)|*.xlsm"
        If (ofd.ShowDialog() = DialogResult.OK) Then
            TextBox1.Text = ofd.FileName
        End If
    End Sub

    Private Sub Comfirm1_Click(sender As Object, e As EventArgs) Handles Comfirm1.Click
        If TextBox1.Text = "" Then
            MessageBox.Show("Wait!", "Please pick your file first!", MessageBoxButtons.OKCancel)
        Else
            ExcelApp = New Application
            ExcelWorkBook = ExcelApp.Workbooks.Open(TextBox1.Text)
            ExcelApp.Visible = False
            ExcelWorkBook.Activate()
            Dim FinderForm As New Form2(ExcelApp)
            FinderForm.Show()
            Me.Hide()
        End If
    End Sub
End Class

2nd Form

Imports Microsoft.Office.Interop.Excel

Public Class Form2
    Property ExcelApp As Application
    Property ExcelWorkBook As Workbook

    Sub New(ByRef App As Application)
        InitializeComponent()
        ExcelApp = App
    End Sub

    Private Sub BBack_Click(sender As Object, e As EventArgs) Handles BBack.Click
        ExcelApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
        ExcelApp = Nothing
        Form1.Show()
        Me.Close()
    End Sub

    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable
        Dim sheets As ExcelApp.Sheets
        Dim oMissiong As Object = System.Reflection.Missing.Value
        Dim workbook As ExcelWorkbook = Nothing
        Dim dt As DataTable = New DataTable()

        Try
            If ExcelApp Is Nothing Then Return Nothing
            workbook = ExcelApp.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
            sheets = workbook.Worksheets
            Dim worksheet As ExcelApp.Worksheet = sheets(1)
            Dim ji As Integer = CType(12, Integer)
            If worksheet Is Nothing Then Return Nothing
            Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
            Dim iColCount As Integer = worksheet.UsedRange.Columns.Count

            For i As Integer = 0 To iColCount - 1
                Dim name = "column" & i

                If hasTitle Then
                    Dim txt = (CType(worksheet.Cells(1, i + 1), ExcelApp.Range)).Text.ToString()
                    If Not String.IsNullOrWhiteSpace(txt) Then name = txt
                End If

                While dt.Columns.Contains(name)
                    name = name & "_1"
                End While

                dt.Columns.Add(New DataColumn(name, GetType(String)))
            Next

            Dim range As ExcelApp.Range
            Dim rowIdx As Integer = If(hasTitle, 2, 1)

            For iRow As Integer = rowIdx To iRowCount
                Dim dr As DataRow = dt.NewRow()

                For iCol As Integer = 1 To iColCount
                    range = CType(worksheet.Cells(iRow, iCol), ExcelApp.Range)
                    dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
                Next

                dt.Rows.Add(dr)
            Next

            Return dt
        Catch
            Return Nothing
        Finally
            workbook.Close(False, oMissiong, oMissiong)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            workbook = Nothing
            ExcelWorkBook.Close()
            ExcelWorkBook = Nothing
        End Try
    End Function

    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged


    End Sub
    Private Sub ComboBox2_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox2.SelectedIndexChanged

    End Sub
    Private Sub ComboBox3_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox3.SelectedIndexChanged

    End Sub
    Private Sub ComboBox4_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox4.SelectedIndexChanged

    End Sub
    Private Sub ComboBox5_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox5.SelectedIndexChanged

    End Sub
    Private Sub ComboBox6_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox6.SelectedIndexChanged

    End Sub

End Class

I think I have not done right the Property and the different Dim, I'm having trouble to integrate the GetDataFromExcelByCom function since I have already open the excel File in Form1.

Answer 7

Hi Biright

I think you should write like this, I have tested it.

1st Form

Imports ExcelApp = Microsoft.Office.Interop.Excel

Public Class Form1
    Dim sheets As ExcelApp.Sheets
    Dim oMissiong As Object = System.Reflection.Missing.Value
    Dim workbook As ExcelApp.Workbook = Nothing
    Dim ofd As OpenFileDialog = New OpenFileDialog()

    Private Sub Browse1_Click(sender As Object, e As EventArgs) Handles Browse1.Click
        ofd.Filter = "Excel Files(.xls)|*.xls|Excel Files(.xlsx)|*.xlsx|Excel Files(*.xlsm)|*.xlsm"
        If (ofd.ShowDialog() = DialogResult.OK) Then
            TextBox1.Text = ofd.FileName
        End If

    End Sub

    Private Sub Comfirm1_Click(sender As Object, e As EventArgs) Handles Comfirm1.Click
        If TextBox1.Text = "" Then
            MessageBox.Show("Wait!", "Please pick your file first!", MessageBoxButtons.OKCancel)
        Else
            'ExcelApp = New Application
            'ExcelWorkBook = ExcelApp.Workbooks.Open(TextBox1.Text)
            'ExcelApp.Visible = False
            'ExcelWorkBook.Activate()
            Dim FinderForm As New Form2()
            FinderForm.Show()
            Me.Hide()
        End If

    End Sub
End Class

2nd Form

Imports ExcelApp = Microsoft.Office.Interop.Excel

Public Class Form2
    'Property ExcelApp As Application
    'Property ExcelWorkBook As Workbook

    'Sub New(ByRef App As ExcelApp.Application)
    '    InitializeComponent()
    '    App = New ExcelApp.Application()
    'End Sub

    Private Sub BBack_Click(sender As Object, e As EventArgs) Handles BBack.Click
        'ExcelApp.Quit()
        'System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp)
        'ExcelApp = Nothing
        'Form1.Show()
        'Me.Close()

    End Sub
    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As DataTable
        Dim sheets As ExcelApp.Sheets
        Dim oMissiong As Object = System.Reflection.Missing.Value
        Dim workbook As ExcelApp.Workbook = Nothing
        Dim dt As DataTable = New DataTable()
        Dim app As ExcelApp.Application = New ExcelApp.Application()
        Dim excelFilePath As String = Form1.TextBox1.Text
        Try
        If app Is Nothing Then Return Nothing
            workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
            sheets = workbook.Worksheets
            Dim worksheet As ExcelApp.Worksheet = sheets(1)
            Dim ji As Integer = CType(12, Integer)
            If worksheet Is Nothing Then Return Nothing
            Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
            Dim iColCount As Integer = worksheet.UsedRange.Columns.Count

            For i As Integer = 0 To iColCount - 1
                Dim name = "column" & i

                If hasTitle Then
                    Dim txt = (CType(worksheet.Cells(1, i + 1), ExcelApp.Range)).Text.ToString()
                    If Not String.IsNullOrWhiteSpace(txt) Then name = txt
                End If

                While dt.Columns.Contains(name)
                    name = name & "_1"
                End While

                dt.Columns.Add(New DataColumn(name, GetType(String)))
            Next

            Dim range As ExcelApp.Range
            Dim rowIdx As Integer = If(hasTitle, 2, 1)

            For iRow As Integer = rowIdx To iRowCount
                Dim dr As DataRow = dt.NewRow()

                For iCol As Integer = 1 To iColCount
                    range = CType(worksheet.Cells(iRow, iCol), ExcelApp.Range)
                    dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
                Next

                dt.Rows.Add(dr)
            Next

            Return dt
        Catch
            Return Nothing
        Finally
            workbook.Close(False, oMissiong, oMissiong)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            workbook = Nothing
            'ExcelWorkBook.Close()
            'ExcelWorkBook = Nothing
            app.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
            app = Nothing

        End Try
    End Function

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
            ComboBox1.Items.Add(GetDataFromExcelByCom(False).Rows(i)(0))
        Next
    End Sub
End Class

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Hello Alex! I tried your code but oddly enough I get some error like "ExcelWorkBook is not declared" or "odf is ambiguous".

Maybe it is because of the project file or something else, I'll try to see what the problem is.

Here's the link to DL the program if you want to have a better picture of the error :

https://drive.google.com/open?id=1LA6o1TMgp7tMd5sbLD4cgoPnRPIrpvPI

And thanks again for your help Alex!

*EDIT*

How about this link?

http://www.mediafire.com/file/4p7szlclgr34rok/OffreFinder%25282018_07_06%2529.rar/file

And yes the Microsoft.Office.Interop.Excel.dll is added

*EDIT2*

Thank you Karen for the info

https://1drv.ms/f/s!Aj-1mwhyikUFaeD1gLbotCII8rI

For the moment I am still trying a few things, though it does not really works.

Answer 9

Hi Biright

I can't open that Link.I don't know where something went wrong.Have you added the com component（Microsoft.Office.Interop.Excel.dll）?

Best Regards

Alex

---

MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 10

For the record, when you need to upload a project, best to use Microsoft OneDrive where you sign in with the account used here. The free version of one drive allows up to 5GB of storage.

Once uploaded you can share it.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 11

Here is a simple example method for loading a combobox:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    LoadComboBoxFromExcel(ComboBox1, "E:\reedk\Documents\Book1.xlsx", "Sheet1", "C", 3, 5)
End Sub

Private Sub LoadComboBoxFromExcel(target As ComboBox, workbookPath As String, workSheetName As String, columnName As String, firstRow As Integer, count As Integer)
    Dim excel As New Microsoft.Office.Interop.Excel.Application
    Dim workbook = excel.Workbooks.Open(workbookPath)
    Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets.Item(workSheetName)
    For i = firstRow To firstRow + count - 1
        Dim cell = CType(worksheet.Cells.Item(i, columnName), Microsoft.Office.Interop.Excel.Range)
        target.Items.Add(cell.Value2.ToString)
    Next
    workbook.Close()
    excel.Quit()
    Runtime.InteropServices.Marshal.ReleaseComObject(excel)
End Sub

Here's what the example data looks like in excel:

As you can see, this example is using column "C" starting at row 3 and taking 5 rows of data.

This should get you started and provide something you can easily expand upon as necessary.

---

Reed Kimble - "When you do things right, people won't be sure you've done anything at all"