Solved

Populate Listbox using excel spreadsheet column VB.net

Posted on 2013-11-04
4
885 Views
Last Modified: 2013-11-20
Good Afternoon,

I'm trying to populate a listbox in VB.net using a excel spreadsheet column. In my code below it gives me an error saying the 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine"

Imports System.Data.OleDb
Imports System.Data
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        OpenFileDialog1.Title = "Please Select a File"
        OpenFileDialog1.FileName = ""
        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
        OpenFileDialog1.ShowDialog()

        Try
            Dim MyConnection As System.Data.OleDb.OleDbConnection
            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source='" & OpenFileDialog1.FileName & "';" & "Extended Properties=""Excel 12.0;HDR=YES;""")
            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [SEA$A:A]", MyConnection)
            MyCommand.TableMappings.Add("Table", "TestTable")

            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)
            ComboBox1.DataSource = DtSet.Tables("TEAML")


            MyConnection.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

Open in new window


Error
0
Comment
Question by:AnthonySmithMCP
4 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 39622684
If you don't have access installed, download and install the runtime version.  It will install the driver for you.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39623148
could it be related to 32/64 bits?

give a try to the free LINQ-to-Excel tool : http://emoreau.com/Entries/Articles/2013/07/A-free-LINQ-to-Excel-and-CSV-provider.aspx
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39623871
Download and install ACE driver and then change your connection string to use ACE driver instead of JET

http://www.microsoft.com/en-gb/download/details.aspx?id=13255
0
 
LVL 1

Author Comment

by:AnthonySmithMCP
ID: 39662264
Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…

832 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