Problem opening Excel 2007 file from OleDbConnection
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(); |
Problem opening Excel 2007 file from OleDbConnection
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. "Dave Jenkins" wrote: 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(); |
All times are GMT +1. The time now is 06:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com