1

I have created a workbook in Excel 2010. This file allows users to enter data on various worksheets and import it in to our systems using a Web Application. The file is connected to using the following connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myfilename.xlsx;Extended Properties='Excel 12.0 XML; HDR=Yes; IMEX=1;'

Everything works fine. However, if the Excel 2010 file is opened in Excel 2007 and simply saved, then the import no longer works. I get the old "Failed to connect to Excel File: External table is not in the expected format." error.

The saved file is not open when I perform the import.

I'm not quite sure what I need to do. The connection string seems correct for Excel 2007 and 2010. Does saving an Excel 2010 file by opening it in Excel 2007 somehow corrupt it? The file extension is the same... xlsx. The workbook is password protected, has hidden sheets, etc. which are necessary.

shenk
  • 33
  • 1
  • 5
  • Check out this solution: http://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format – Carl Prothman May 17 '15 at 21:49
  • Thanks for the info, however, I believe I am already using the correct connection string for Excel 2007/2010. – shenk May 18 '15 at 02:18
  • Hmmm, two things come to mind. 1) the file needs to be saved in correct format (resave?) or 2) you need to add "xml" to extended properties. e.g Excel 12.0 Xml;HDR=YES;IMEX=1; Make sure to remove spaces. – Carl Prothman May 20 '15 at 06:16
  • If you look at the connection string I posted, I do include "XML", I tried removing the spaces after the semi-colons, as you suggested, and it doesn't seem to have made a difference. As for the file save, this appears to be the issue. The file is an Excel 2010 file. If it is opened in Excel 2007, modified and saved, it somehow becomes corrupted and cannot be opened through the connection string. I imagine if the source file were Excel 2007, this would not happen. But I do not have access to and Excel 2007 version of the original file. – shenk May 20 '15 at 11:27

0 Answers0