Passing data from Excel Sheet to DataGridView using VB.NET

Question

I am working on a project that aims to import and export data from a datagridview to excel table and viceversa. So far, I managed to send data from my datagridview to excel, but I just can't do it from excel to datagrid... I tried the following code:

Dim dtSheet1 As New DataTable
    Using cn As New System.Data.OleDb.OleDbConnection
        Dim Builder As New OleDbConnectionStringBuilder With
            {
                .DataSource = filename,
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        Builder.Add("Extended Properties", "Excel 16.0; IMEX=1;HDR=Yes;")
        cn.ConnectionString = Builder.ConnectionString

        cn.Open()

        Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
            cmd.CommandText = "SELECT * FROM [Sheet1$]"
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader

            dtSheet1.Load(dr)
            datagridview1.DataSource = dtSheet1
        End Using
    End Using

But I keep encountering the exception: (and I tried changing the target of my project from Any CPU to x64...)

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll Additional information: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

So: is there any way I could send data from excel to datagrid WITHOUT USING OLEDB? If so, I would be so grateful for a solution that could get me out of this, already working on this for 3 days...

Have nice day! (:

You could look at the connection strings here for help: connectionstrings.com/excel-2007 — Derek
– Derek, Commented Jul 14, 2017 at 11:19
Thanks, Derek! I think those strings will be quite helpful, now I just have to figure out how to change my code depending on the new connection string structure ;) — Gabriel Stancu
– Gabriel Stancu, Commented Jul 14, 2017 at 12:00

Derek · Accepted Answer · 2017-07-14 12:12:33Z

1

Actually this is even better:

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

See the selected answer there. Make sure to read the comments underneath it.

answered Jul 14, 2017 at 12:12

Derek

9,0837 gold badges40 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing data from Excel Sheet to DataGridView using VB.NET

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related