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 ?
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.
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
Re: How to add items into combo box from a column in a Excel file
Quote:
Originally Posted by
kevininstructor
' 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.
Re: How to add items into combo box from a column in a Excel file
Quote:
Originally Posted by
jmcilhinney
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 :-)
Re: How to add items into combo box from a column in a Excel file
Quote:
Originally Posted by
kevininstructor
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. :)
Re: How to add items into combo box from a column in a Excel file
Good to hear this worked for you.
Re: How to add items into combo box from a column in a Excel file
