Problem opening Excel 2007 file from OleDbConnection

[Dave Jenkins]

I am attempting to open an Excel 2007 file (test1.xlsx) from my Word 2007
addin using OleDb. The code runs fine as long as I have the Excel 2007
application running with "test1.xlsx" open. But if Excel 2007 is not
running, I get an exception "External table is not in the expected format" on
the myConn.Open() statement. If I replace the Excel 2007 file with the
equivalent Excel 2003 binary file it always works.

Do I need a new JET engine to open Excel 2007 files? Why does the code work
when Excel 2007 has the file open? Does the old JET engine notice that Excel
2007 has the file open and use its code? Thanks for any help.

Here is the code:

string filename = @"c:\test1.xlsx";
if (!File.Exists(filename))
throw new Exception("File not found");

string myConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=YES\"";
OleDbConnection myConn = new OleDbConnection(myConnStr);
myConn.Open(); // HERE IS WHERE IT FAILS
myConn.Close();

 

[Dave Jenkins]

I think I found the solution. It works if you replace the connection string
with "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES\"". Evidently, there's an ACE
engine that's replacing the JET engine. I found this on
http://msdn2.microsoft.com/en-us/library/aa395290.aspx.