Hi,
I have an application that imports data from an Excel spreadsheet and displays it in an AdvancedDataGridView.

I initially used OLEDB 4.0 in my connection string and the excel file was saved as xls. This worked fine.

I then added another Excel file containing some look up data which I needed to edit and export as an excel file but the OLEDB.4.0 wouldn't save as xls and if I saved it as xlsx then it couldn't be opened with the OLEDB.4.0.

I found how to upgrade to OLEDB.12.0 that should be OK with xlsx but now it cannot open the Excel files and gives the error that the Sheet0 cannot be found, I know that sheet name is the only sheet in the file because it was working fine with OLEDB.4.0 so it's not the sheet name missing or miss-spelled.

My OLEDB.4.0 connection code that works:
Code:
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fn & "';Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet0$]", CType(MyConnection, OleDb.OleDbConnection))
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
AdvancedDataGridView1.DataSource = DtSet.Tables(0)
Fn is the full server filename path to a share drive.

My OLEDB.12.0 connection code that doesn't work:
Code:
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Ace.OLEDB.12.0;Data Source='" & fn1 & "';Extended Properties='Excel 12.0;'")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet0$]", CType(MyConnection, OleDb.OleDbConnection))
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
AdvancedDataGridView1.DataSource = DtSet.Tables(0)
The error I get:
=============================================
The Data could not be loaded
Error message:
'Sheet0' is not a valid name. Make sure that it does not include invalid
characters or punctuation and that it is not too long.
=============================================

Any suggestions would be gratefully recieved, I am not a proffessional programmer I'm the dreaded click and learn self taught type.

Thanks.