Results 1 to 8 of 8

Thread: How to add items into combo box from a column in a Excel file

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2013
    Posts
    10

    Question How to add items into combo box from a column in a Excel file

    I am very new to VB. I want to load items in a combo box from a column in a Excel file , Can anyone let me know how we can do it ?

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to add items into combo box from a column in a Excel file

    You can read data from Excel using ADO.NET. To learn the basics of ADO.NET, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data. If you use a data reader then you can add each item to the ComboBox directly, otherwise you can bind a DataTable to the ComboBox. Visit www.connectionstrings.com to learn how to connect to Excel specifically.

  3. #3
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: How to add items into combo box from a column in a Excel file

    Example, we want to read data from sheet1, column A is first name, column B is last name, column C is age. The Excel file (change the name to your file name) in this example is in the same folder as the executable, if diffrerent you would alter the DataSource property in the Builder variable.

    VS2010 code, Option Strict On, Option Infer On
    Requires 1 ComboBox, 2 Labels

    Code:
    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "File1.xlsx")
                }
    
            ' Note HRD=No indicates the first row of the sheet is not data
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
    
            Dim SheetName As String = "Sheet1"
            Dim dt As New DataTable
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                cn.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand(
                    <Text>
                        SELECT 
                            F1 As FirstName, 
                            F2 As LastName, 
                            F3 As Age  
                        FROM [<%= SheetName %>$]
                    </Text>.Value,
                    cn
                )
    
                dt.Load(cmd.ExecuteReader)
                dt.DefaultView.Sort = "LastName asc"
            End Using
    
            ComboBox1.DisplayMember = "LastName"
            ComboBox1.DataSource = dt
    
            Label1.DataBindings.Add("Text", dt, "FirstName")
            Label2.DataBindings.Add("Text", dt, "Age")
    
        End Sub
    End Class

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to add items into combo box from a column in a Excel file

    Quote Originally Posted by kevininstructor View Post
    ' Note HRD=No indicates the first row of the sheet is not data
    You've got that the wrong way around. "HDR=Yes" means that the first row contains column headers rather than data while "HDR=No" means that there are no column headers and the first row of the sheet contains the first row of the data.

  5. #5
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: How to add items into combo box from a column in a Excel file

    Quote Originally Posted by jmcilhinney View Post
    You've got that the wrong way around. "HDR=Yes" means that the first row contains column headers rather than data while "HDR=No" means that there are no column headers and the first row of the sheet contains the first row of the data.
    I know what HDR=No means (done enough of them) but not sure for the life of me wrote down the opposite, must be from getting old :-)

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2013
    Posts
    10

    Resolved Re: How to add items into combo box from a column in a Excel file

    Quote Originally Posted by kevininstructor View Post
    Example, we want to read data from sheet1, column A is first name, column B is last name, column C is age. The Excel file (change the name to your file name) in this example is in the same folder as the executable, if diffrerent you would alter the DataSource property in the Builder variable.

    VS2010 code, Option Strict On, Option Infer On
    Requires 1 ComboBox, 2 Labels

    Code:
    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim Builder As New OleDbConnectionStringBuilder With
                {
                    .Provider = "Microsoft.ACE.OLEDB.12.0",
                    .DataSource = IO.Path.Combine(Application.StartupPath, "File1.xlsx")
                }
    
            ' Note HRD=No indicates the first row of the sheet is not data
            Builder.Add("Extended Properties", "Excel 12.0; HDR=No;")
    
            Dim SheetName As String = "Sheet1"
            Dim dt As New DataTable
    
            Using cn As New OleDbConnection With
                {
                    .ConnectionString = Builder.ConnectionString
                }
                cn.Open()
    
                Dim cmd As OleDbCommand = New OleDbCommand(
                    <Text>
                        SELECT 
                            F1 As FirstName, 
                            F2 As LastName, 
                            F3 As Age  
                        FROM [<%= SheetName %>$]
                    </Text>.Value,
                    cn
                )
    
                dt.Load(cmd.ExecuteReader)
                dt.DefaultView.Sort = "LastName asc"
            End Using
    
            ComboBox1.DisplayMember = "LastName"
            ComboBox1.DataSource = dt
    
            Label1.DataBindings.Add("Text", dt, "FirstName")
            Label2.DataBindings.Add("Text", dt, "Age")
    
        End Sub
    End Class
    Thank you very much kevininstructor . It is working perfectly.

  7. #7
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: How to add items into combo box from a column in a Excel file

    Good to hear this worked for you.

  8. #8
    New Member
    Join Date
    Aug 2016
    Posts
    14

    Re: How to add items into combo box from a column in a Excel file

    1111
    Last edited by leon82; Sep 9th, 2016 at 02:35 AM. Reason: delete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width