tiehaze
asked on
Adding excel values to listbox in windows form.
I am trying to load in a named range from excel to a listbox in my windows form. Here is what I have:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
ws = DBEngine.Workspaces(0)
Dim NoOfRecords As Long
' Open the database
db = ws.OpenDatabase("C:\Test\B
' Retrieve the recordset
rs = db.OpenRecordset("SELECT * FROM `myDatabase`")
' Determine the number of retrieved records
With rs
.MoveLast()
NoOfRecords = .RecordCount
.MoveFirst()
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close()
db.Close()
rs = Nothing
db = Nothing
End Sub
The first error is that the name DBEngine is not declared.
The other error is 'Column' is not a member of System.Windows.Forms.Listb
Any ideas how to fix this?
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
ws = DBEngine.Workspaces(0)
Dim NoOfRecords As Long
' Open the database
db = ws.OpenDatabase("C:\Test\B
' Retrieve the recordset
rs = db.OpenRecordset("SELECT * FROM `myDatabase`")
' Determine the number of retrieved records
With rs
.MoveLast()
NoOfRecords = .RecordCount
.MoveFirst()
End With
' Set the number of Columns = number of Fields in recordset
ListBox1.ColumnCount = rs.Fields.Count
' Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
' Cleanup
rs.Close()
db.Close()
rs = Nothing
db = Nothing
End Sub
The first error is that the name DBEngine is not declared.
The other error is 'Column' is not a member of System.Windows.Forms.Listb
Any ideas how to fix this?
Like 'theplonk' already suggested, use oledb. DAO is already old-school (and maybe in short time not supported any more by microsoft).
If you want to get the best results in performance, ... use the ADO.NET way
If you want to get the best results in performance, ... use the ADO.NET way
ASKER
What is the ADO.Net way? I have been training myself in VB.Net, is that the same?
Hi tiehaze;
Here is a solution using ADO.Net. A couple of things to note.
1. conString has as the Source of the Excel file to be Book1.xls, This file is located in the same directory as the program executable file.
2. In the line cmd.CommandText the SQL Select statement has a Value1 which is the column name which must be in row 1 of that column. Also the value [TableValues$] is the name of the excel sheet where Excel defaults to SheetX where X is a number. So if your sheet name is Sheet1 then you need to replace [TableValues$] with [Sheet1$]
3. At the end of the code sample I used ListBox1, this will need to be changed to the list box you are using.
Imports System.Data.Common
Dim conString As String = "Provider=Microsoft.Jet.OL
Dim dbFactory As DbProviderFactory = DbProviderFactories.GetFac
"System.Data.OleDb")
Dim adapter As DbDataAdapter = dbFactory.CreateDataAdapte
Dim cmd As DbCommand = dbFactory.CreateCommand()
cmd.CommandText = "Select Value1 From [TableValues$]"
Dim conn As DbConnection = dbFactory.CreateConnection
conn.ConnectionString = conString
cmd.Connection = conn
adapter.SelectCommand = cmd
Dim excelDS As DataSet = New DataSet
adapter.Fill(excelDS)
ListBox1.Items.Clear()
ListBox1.DataSource = excelDS.Tables(0)
ListBox1.DisplayMember = "Value1"
Fernando
Here is a solution using ADO.Net. A couple of things to note.
1. conString has as the Source of the Excel file to be Book1.xls, This file is located in the same directory as the program executable file.
2. In the line cmd.CommandText the SQL Select statement has a Value1 which is the column name which must be in row 1 of that column. Also the value [TableValues$] is the name of the excel sheet where Excel defaults to SheetX where X is a number. So if your sheet name is Sheet1 then you need to replace [TableValues$] with [Sheet1$]
3. At the end of the code sample I used ListBox1, this will need to be changed to the list box you are using.
Imports System.Data.Common
Dim conString As String = "Provider=Microsoft.Jet.OL
Dim dbFactory As DbProviderFactory = DbProviderFactories.GetFac
"System.Data.OleDb")
Dim adapter As DbDataAdapter = dbFactory.CreateDataAdapte
Dim cmd As DbCommand = dbFactory.CreateCommand()
cmd.CommandText = "Select Value1 From [TableValues$]"
Dim conn As DbConnection = dbFactory.CreateConnection
conn.ConnectionString = conString
cmd.Connection = conn
adapter.SelectCommand = cmd
Dim excelDS As DataSet = New DataSet
adapter.Fill(excelDS)
ListBox1.Items.Clear()
ListBox1.DataSource = excelDS.Tables(0)
ListBox1.DisplayMember = "Value1"
Fernando
One other thing with item 1 above with Source=Book1.xls; if the Excel file is not in the same directory as the executable then it will need to be fully qualified. For example if the excel file is in C:\Temp then change Source=Book1.xls; to Source=C:\Temp\Book1.xls;
ASKER
Is the excel file bound to the listbox?
The excel data is read into a DataTable and stored in memory then the ListBox is told where the data is and which column, if multiple columns in table, to displsy.
ASKER
So if I delete a row from the listbox, will it delete the corresponding row from excel?
I have a minor problem with my code. Add the following line to the end of the code sample
adapter.Fill(excelDS)
ListBox1.DataSource = Nothing ' <===== Add this line
ListBox1.Items.Clear()
ListBox1.DataSource = excelDS.Tables(0)
ListBox1.DisplayMember = "Value1"
adapter.Fill(excelDS)
ListBox1.DataSource = Nothing ' <===== Add this line
ListBox1.Items.Clear()
ListBox1.DataSource = excelDS.Tables(0)
ListBox1.DisplayMember = "Value1"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/questions/22393341/Reading-Excel-Named-Ranges-via-OLEDB-in-VB-NET-2005.html