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

  • 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.

    Wednesday, July 4, 2018 2:48 PM

All replies

  • 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"

    Wednesday, July 4, 2018 3:16 PM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 4, 2018 3:53 PM
  • 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 MSDNFSF@microsoft.com.

    Thursday, July 5, 2018 3:05 AM
  • Thanks for the reply Reed! I'll look into it!

    • Edited by Biright Thursday, July 5, 2018 7:05 AM
    Thursday, July 5, 2018 7:02 AM
  • Thx for the reply Karen! That's quite helpful, I'll see what I can learn.
    Thursday, July 5, 2018 7:05 AM
  • 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.


    • Edited by Biright Thursday, July 5, 2018 12:01 PM
    Thursday, July 5, 2018 7:15 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, July 6, 2018 1:07 AM
  • 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.

    • Edited by Biright Monday, July 9, 2018 8:22 AM
    Friday, July 6, 2018 7:27 AM
  • 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 MSDNFSF@microsoft.com.

    Friday, July 6, 2018 7:43 AM
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, July 6, 2018 12:16 PM
  • 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"

    Friday, July 6, 2018 1:54 PM