Click here to Skip to main content
15,113,531 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
UP until a week or so ago, everything worked and now nothing is working, which leads me to suspect that Microsoft has changed something in the Net Framework, Windows 10 Anniversary Update or in the PIA's. In the past, I've just used the wizard to link my data in an Excel spreadsheet to the DataGridView control and it just worked. But about a week ago, all of a sudden, my program would not load the COM library with early binding, but I was able to get it to work with late binding. But it clobbered my DataGridView (dgv) display and I have not been able to get it back.

What I have tried:

Now, if I use the wizard, I just get an empty dgv with no data. If I try to do it in code, the command OleDbAdapter.Fill(dataset) no longer works and it throws an exception. Here's the code I'm using....
VB
Imports System.Data.OleDb

Public Class Form1

    Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Dave\Documents\clients.xls"
    Dim Myconn As OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim src As New BindingSource

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Myconn = New OleDbConnection
        Myconn.ConnectionString = con
        ds = New DataSet
        tables = ds.Tables
        da = New OleDbDataAdapter("Select * from [clients]", Myconn)
        da.Fill(ds, "clients")  'this step fails and throws the exception
        Dim view As New DataView(tables(0))
        src.DataSource = view
        dgv.DataSource = view

    End Sub

End Class
Posted
Updated 8-Dec-16 5:43am
v2
Comments
Richard MacCutchan 30-Nov-16 4:23am
   
I wonder what the exception could be?
Member 12877669 30-Nov-16 15:10pm
   
Funny thing! I removed office365 and went back to Office 2013 and now all of a sudden all of my programs work again. So it looks like MS is "improving" things, sort of like what they did to VB6.
Mehdi Gholam 30-Nov-16 5:33am
   
Try reinstalling the OLEDB provider again.
Member 12877669 30-Nov-16 15:12pm
   
Funny thing! I removed office365 and went back to Office 2013 and now all of a sudden all of my programs work again. So it looks like MS is "improving" things, sort of like what they did to VB6. I tried to reinstall the OLEDB but no luck at all. I don't know why Office 2013 is so different than Office 2016, but it is.
Member 12877669 30-Nov-16 15:09pm
   
Funny thing! I removed office365 and went back to Office 2013 and now all of a sudden all of my programs work again. So it looks like MS is "improving" things, sort of like what they did to VB6.
Majesss 12-Dec-16 7:47am
   
I presume you had an issue with an older reference being used, when working with Microsoft Excel Interop in C# or VB.NET you need to update your references when updating Microsoft Office version.
You can however avoid this if you change your approach, for example by using this C# and VB.NET Excel library you could directly export your Excel data into a DataGridView control.

1 solution

You should give EPPLUS a try. I use it in almost all my projects to read excel files.

With only a few lines of code you can read excel files.

Download the EPPLUS.dll and store it in your project folder.
Add it to the reference via Add Reference menu

In you module/class/form write:

VB.NET
Imports OfficeOpenXml


Create a sub to Read File - pass the file name
VB.NE
Private Sub ReadFile(txt_filepath as string)
Try
Using pck As New ExcelPackage()
                'Open the Excel file and load it to the ExcelPackage'
                Using stream = File.OpenRead(txt_filepath)
                    pck.Load(stream)
                End Using

                Dim ws As ExcelWorksheet = pck.Workbook.Worksheets(1)
                Dim myDT As DataTable = WorksheetToDataTable(ws, True)
End Using
Catch ex As Exception
            MessageBox.Show("Could not read data. Original error: " + ex.Message)
        End Try
end sub


'This function reads the file and converts it into a DataTable which you can use or bind to your datagrid directly
VB.NE
Public Function WorksheetToDataTable(ByVal ws As ExcelWorksheet, Optional ByVal hasHeader As Boolean = True) As DataTable
     Dim dt As New DataTable(ws.Name)
     Dim totalCols As Integer = ws.Dimension.[End].Column
     Dim totalRows As Integer = ws.Dimension.[End].Row
     Dim startRow As Integer = If(hasHeader, 2, 1)
     Dim wsRow As ExcelRange
     Dim dr As DataRow
     For Each firstRowCell As Object In ws.Cells(1, 1, 1, totalCols)
         dt.Columns.Add(If(hasHeader, firstRowCell.Text, String.Format("Column {0}", firstRowCell.Start.Column)))
     Next

     For rowNum As Integer = startRow To totalRows
         wsRow = ws.Cells(rowNum, 1, rowNum, totalCols)
         dr = dt.NewRow()
         For Each cell As Object In wsRow
             dr(cell.Start.Column - 1) = cell.Text
         Next

         dt.Rows.Add(dr)
     Next

     Return dt
 End Function


There are more samples our there - but this one gets the job done

One downside is that it can only read Excel files formatted as HTML (xlsx). It would not be able to read XLS file without considerable work.

Hope this helps.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900