How to use ACE OLEDB to import Excel data into VB?

Question

Originally I was using Office Interop to import data, but that was a headache and a half for both me and my computer. Right now I'm attempting to load it with ACE, but my data grid isn't being populated. Once that's up and running I need to know how to use that data in other ways, and how to grab specific cells of data from that DataSet. I'm using Visual Studio 2008, by the way.

Right now I have...

Public Function funcUpdate(ByVal sFileLoc As String) As Boolean
    'Determine connection string properties
    Dim dbProperty As String
    If updFileExt = ".xlsx" Then
        dbProperty = "Excel 12.0 Xml;HDR=No"
    ElseIf updFileExt = ".xls" Then
        dbProperty = "Excel 12.0;HDR=No"
    Else
        MessageBox.Show("FATAL: File type error on updater", "OHGAWDNO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        updateTerm()
        Return False
    End If
    Dim dbConn As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & updFile & ";Extended Properties=" & dbProperty & ";")
    Dim dbCommand As New OleDb.OleDbDataAdapter("select * from [sheet1$]", dbConn)
    Dim dtSet As New DataSet
    Try
        dbCommand.TableMappings.Add("Table", "ExcelTest")
        dbCommand.Fill(dtSet)
        Form1.DataGrid1.DataSource = dtSet.Tables(0)
    Catch exlErr As Exception
    Finally
        dbConn.Close()
    End Try

    updateTerm()
End Function

I removed the exception catch and found that there was an issue in my connection string (no "" around the filename and properties), but the datagrid still isn't being populated. — Brandon, Nov 13 '10 at 15:10
I checked whether dtSet contained any data and confirmed that it does. It must be a problem with displaying on the datagrid. — Brandon, Nov 13 '10 at 16:18

score 0 · Answer 1 · answered Feb 02 '15 at 12:09

Try to do this.

Dim path As String = "c:\example.xlsx"
Dim constr As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;" & _
                                           "HDR=YES;IMEX=1;""", path)
Dim adapter As New OleDbDataAdapter
Using cn As New System.Data.OleDb.OleDbConnection(constr)

        Try
            cmdselcet = New OleDbCommand("SELECT * FROM [Sheet1$]", cn)
            cn.Open()

            adapter.SelectCommand = cmdselcet
            Dim ds As DataSet
            ds = New DataSet

            'Display
            adapter.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)
            MsgBox("Done!")

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Using

How to use ACE OLEDB to import Excel data into VB?

1 Answers1