Importing from Excel file

[bartdedecker]

Hi,

I have a strange problem. I wrote an import procedure to import data from an Excel file. It worked already without any problem. Today, I want to do the same import and it doesn't work anymore... :-s

this is my code:

Dim myDT As New DataTable("Artikels")
Dim excelConnection As New OleDbConnection
    Dim excelAdapter As New OleDbDataAdapter

    excelConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\TEST.XLS; " & _
      "Extended Properties=Excel 8.0;")

        excelAdapter = New System.Data.OleDb.OleDbDataAdapter( _
              "select * from [Sheet1$]", excelConnection)
    Try
      excelAdapter.Fill(myDT)
    Catch ex As Exception
            Dim errorWindow As New frmErrorWindow
            errorWindow.Message = ex.ToString
            errorWindow.ShowDialog()
    End Try
    excelConnection.Close()

This is the error I get:

System.Data.OleDb.OleDbException: 'Sheet1$' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at ArtikelBeheerCheyns.frmArticleFiche.btnImport_Click(Object sender, EventArgs e) in D:\Working...

What can be the error??

 

[Sheco]

'Sheet1$' is not a valid name....

What are the names of the sheets in your excel workbook?

 

[bartdedecker]

There is only one sheet. I renamed the sheet in "test", but it don't work yet.

 

[StanleyClarke]

It seems to be a foolish advise, but it's working.
Just change your regional settings to English (US) and enjoy the difference.