-
Jun 22nd, 2013, 04:20 AM
#1
Thread Starter
New Member
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 ?
-
Jun 22nd, 2013, 04:37 AM
#2
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.
-
Jun 22nd, 2013, 11:06 AM
#3
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
-
Jun 22nd, 2013, 11:20 AM
#4
Re: How to add items into combo box from a column in a Excel file
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.
-
Jun 22nd, 2013, 08:37 PM
#5
Re: How to add items into combo box from a column in a Excel file
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 :-)
-
Jun 22nd, 2013, 11:42 PM
#6
Thread Starter
New Member
Re: How to add items into combo box from a column in a Excel file
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.
-
Jun 23rd, 2013, 06:11 AM
#7
Re: How to add items into combo box from a column in a Excel file
Good to hear this worked for you.
-
Aug 30th, 2016, 12:47 AM
#8
New Member
Re: How to add items into combo box from a column in a Excel file
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|