OLEDB.12.0 importing Excel data cannot find Sheet0

[gadjet]

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:

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:

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.

[wes4dbt]

Fn is the full server filename path to a share drive.

But your using "fn1".

[jdc2000]

Open the Excel file manually in Excel. Check to see what the actual sheet name is that you want to use. Replace the sheet name you have on the second line of your code with the correct sheet name from the Excel file. See what happens then.

[gadjet]

Open the Excel file manually in Excel. Check to see what the actual sheet name is that you want to use. Replace the sheet name you have on the second line of your code with the correct sheet name from the Excel file. See what happens then.

Thanks for the suggestion but, as I said, I know that the sheet name is absolutely correct already.

[gadjet]

But your using "fn1".

Sorry, Typo .............. although I will check it in case I've also got a typo in the code, Hmmmm.

[gadjet]

But your using "fn1".

I can't test it until Tuesday but I think you hit the nail on the head, I use two filenames in the solution one "fn" and another "fn1" and I think I made a copy and paste error and mixed them up which would explain the issue I'm having, well spotted :-)