0

I have the following problem, by using the connection string:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0 Xml;"

Then, I can execute the open-task. Nevertheless, if I want to use the following connection string:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=Excel 12.0 Xml;HDR=NO"

I get a mistake, saying me that the installable isam could not be found.

What is wrong with the second connection string, as I need this one because in my worksheet no headers are used.

Thanks in advance

Han
  • 3,052
  • 2
  • 22
  • 31
Claude G
  • 23
  • 6

1 Answers1

0

if you want to query excel you must have a '$' sign after the sheet name in order to define the sheet as a table. anyway that is the code i wrote for that purpose:

 Try
        Dim Myconnetion As New OleDbConnection
        Dim DataSet As System.Data.DataSet
        Dim MyCOmmand As System.Data.OleDb.OleDbDataAdapter
        Dim Path As String = fullpath

        Myconnetion = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties=Excel 12.0;")
        MyCOmmand = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & "[" & sheetname & "$]")

        DataSet = New System.Data.DataSet
        MyCOmmand.Fill(DataSet)
        dgv.DataSource = DataSet.Tables(0)
        Myconnetion.Close()


    Catch ex As Exception
        MsgBox(ex.Message.ToString)
    End Try
  • Thanks, so the '$' says that my sheet in excel has to be considered as a table and all the row and columns has to be considered even the first one? – Claude G Aug 05 '15 at 13:00
  • yes, also the headers must be in the first row of the sheet in order to recognized as columns of the table. –  Aug 05 '15 at 13:04