?
Solved

Adding excel values to listbox in windows form.

Posted on 2007-08-08
10
Medium Priority
?
354 Views
Last Modified: 2013-11-27
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\Book1.xls", False, False, "Excel 8.0")

        ' 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.Listbox

Any ideas how to fix this?
0
Comment
Question by:tiehaze
10 Comments
 
LVL 8

Expert Comment

by:theplonk
ID: 19658501
This might be another method of getting the values for you, using oledb.

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_22393341.html
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 19659937
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
0
 

Author Comment

by:tiehaze
ID: 19662473
What is the ADO.Net way? I have been training myself in VB.Net, is that the same?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 64

Expert Comment

by:Fernando Soto
ID: 19663235
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.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"""
        Dim dbFactory As DbProviderFactory = DbProviderFactories.GetFactory( _
            "System.Data.OleDb")
        Dim adapter As DbDataAdapter = dbFactory.CreateDataAdapter()
        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
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 19663265
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;
0
 

Author Comment

by:tiehaze
ID: 19663290
Is the excel file bound to the listbox?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 19663384
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.
0
 

Author Comment

by:tiehaze
ID: 19663399
So if I delete a row from the listbox, will it delete the corresponding row from excel?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 19663438
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"
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 19663572
To your question, "So if I delete a row from the listbox, will it delete the corresponding row from excel?"

No it will not. ADO .Net in this case is working in a disconnected mode. You would have to explicitly tell the DataSet to do an Update.

Also; because we are using the ListBox DataSource you can not directly delete it from the ListBox you will need to delete it from the DataTable like in the following code.

excelDS.Tables(0).Rows.RemoveAt(2)

Also when you delete it from the DataTable if excelDS is not in the same scope of where it was defined then you will need to define excelDS at form level.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question