import data from excel 2003 to dataTable

Question

I am importing excel worksheets to a datagridview using the following code:

Private Sub Browsimportbtn_Click(sender As Object, e As EventArgs) Handles Browsimportbtn.Click
    Dim textpath As String
    Dim textpath1 As String

    Dim opf As New OpenFileDialog
    If opf.ShowDialog = 1 Then
        textpath = opf.FileName
        textpath1 = opf.SafeFileName
        textpath1 = textpath1.Remove(textpath1.Length -4,4)

        Dim cnexcell As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & textpath & "; Extended Properties = ""Excel 12.0 Xml;HDR=YES"";")
        Dim cmdE As New OleDbCommand("SELECT * FROM Feuil1", cnexcell)

        Try

            Dim daoledb As New OleDbDataAdapter
            Dim dset As New DataSet


            daoledb.SelectCommand = cmdE
            daoledb.Fill(dset, "Feuil1")
            DGVmodele.DataSource = dset.Tables("Feuil1")

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

    End If

End Sub

The code above is working well with .XLSX files (office 2007,2010...) but do not with .XLS and i don't know where is the issue.

Any suggestions?

First read [ask] and take the [tour] - surely you get an error message or something? — Ňɏssa Pøngjǣrdenlarp, Jan 04 '18 at 20:04

Hadi · Answer 1 · 2018-01-04T20:29:34.033

0

The issue is in the connectionstring.

You are specifying the Excel 12 as a version in the connection string which is related to 2007 or higher. Try using this function to build the connectionstring

Public Function BuildConnectionString(Byval m_strExcelPath as String) As String
    If m_strExcelPath.Substring(m_strExcelPath.LastIndexOf(".")).ToLower = ".xlsx" Then
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & m_strExcelPath & ";Excel 12.0;HDR=YES;IMEX=1"
    Else
        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_strExcelPath & ";Excel 8.0;HDR=YES;IMEX=1"
    End If
End Function

edited Jan 04 '18 at 20:29

answered Jan 04 '18 at 20:05

Hadi

36,233
13
65
124

Thank you Hadi but i tried to use the second connection string and i got the same problem that i got the error "ISAM" introuvable and tried the solution shared in Windows site (changing A .dll register ) but the the error still occuring – Omri Maher Jan 08 '18 at 07:54
install https://www.microsoft.com/en-us/download/details.aspx?id=13255 or https://www.microsoft.com/en-us/download/details.aspx?id=54920 – Hadi Jan 08 '18 at 10:14
and make sure that the microsoft excel installed language is the same of the culture info of the running thread (main thread) – Hadi Jan 08 '18 at 10:15
1

Thank you it's done converting online XLS files to XLSX ones – Omri Maher Jan 17 '18 at 14:37

Shan Coralde · Answer 2 · 2018-01-25T08:56:36.737

0

Try using this instead.

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & m_strExcelPath &  ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'"

It is unadvisable to use the JET OLEDB because it is outdated. If you really want to use it, you might need to reinstall Access Database Engine 2003.

Also, I heard there's also a bug regarding this.

edited Jan 25 '18 at 08:56

answered Jan 25 '18 at 08:10

Shan Coralde

214
2
15

import data from excel 2003 to dataTable

2 Answers2

Linked