OLEDB Connection String Fails - Except When Excel Is Open…?

1
votes

I'm trying to programatically read the contents of an Excel spreadsheet (.xlsm format) using the Microsoft.Jet.OLEDB.4.0** in C#.

My connection string is:

Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES;IMEX=1;"";Data Source="C:\Test.xlsm"

When I execute my code, it fails with an exception stating:

OldDbException was unhandled by user code

External table is not in the expected format.

However - if I open the Test.xlsm file in Excel and re-run my code, it works with no exception and performs as intended. No problems, works great.

  • Why/How does having a particular file open in Excel change how my code is accessing it?
  • What is the correct way to open a .xlsm file with the Microsoft Jet OLEDB provider?

Additional Information: Running Windows 7 x64 / Excel 2010

1
c#.netexceloledb
This might go back to the same problem I have using *.xlsm fies as sources for Mailmerge .... Try to save your data as *.xlsb and use this as a source .... might be a security issue with *.xlsm files – MikeD

1 Answers

0
votes

The "Excel 8.0;" part of the connection string refers to Excel 2003 and earlier files (i.e. .xls extension)

You might want to try the Excel 2007 onwards connection string and use the version specifically for .xlsm files:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\test.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=YES"";

(The "" is neeeded to escape the " character)

Excel connection strings are usually written in the order Provider; Data Source; Extended Properties. The connection string you have been using is in a different order and also seems to have some inconsistent handling of " characters in it. Your original connection string should probably be written as:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test.xlsm;Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"";

and even then it might not work with .xlsm files