0

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! (:

Gabriel Stancu
  • 940
  • 2
  • 13
  • 26
  • You could look at the connection strings here for help: https://www.connectionstrings.com/excel-2007/ – Derek Jul 14 '17 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 Jul 14 '17 at 12:00

1 Answers1

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.

Derek
  • 7,615
  • 5
  • 33
  • 58