Search code examples
interopexcel-2007oledbvb.net-2010excel-2010

Connection string not working when saving to 2007 format

I'm very confused with OleDB & Excel.

I've 2 files:

One is saved in Excel 2007
One is saved in Excel 2010

This is because some computers have 2007 and other 2010 version of Microsoft Excel.

In vb.net i use:

 m_sConn1 = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';", file)

When is do:

conn1 = New System.Data.OleDb.OleDbConnection(m_sConn1)
conn1.open()

With the save-in-2010 file i got 0 errors. With the one saved in 2007 i got 

"External table is not in the expected format"

Is this string connection for both versions? Should i use Interop instead? (because the file is auto generated using Interop Excel 12.0 and it works in 2007 & 2010).

I already installed 2007 Office System Driver: Data Connectivity Components from http://www.microsoft.com/en-us/download/details.aspx?id=23734

Edit:

  • If i open the 2007 file in 2010 and save it, oleDB reads it ok.
Solution

  • It's a Bug already reported. Only Excel 2007 has this error and MS knows it but never addressed it.

    This project can be only use w/ excel 2007.