There is a little trick to connecting to a password protected .xls Excel spreadsheet using the Jet 4.0 OLEDB driver.

 

If one uses the obvious Password=xxx;User Id=Admin you will get an error “Workgroup Information file missing or file is in use by another user”.

 

The ADOConnection will return an active connection is using the following connection string but in Delphi 2007 I am still getting an error on trying to read the file “Could not decrypt file”.

 

So here is the Connection String which will open the connection successfully in Read Only mode:

 

Provider:=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\MyExcel.xls;Mode=Read;Extended Properties=Excel 8.0;Jet OLEDB:Database Password="xxx";

 

It appears that “Could not decrypt file” error is due to a well known limitation of the Jet OLEDB 4.0 engine in that it always returns this error on trying to read a password protected Excel file even if passwords are known.

The hack around this is to open the file in Excel first in full read/write mode BEFORE opening the TADOConnection – just opening it in READ ONLY mode in Excel WILL NOT work!

This is not only an issue in Delphi but in any application that uses Jet 4.0 OLEDB engine.

Of course, another solution is to use COM OLE automation such as:

  • CreateOLEObject method – see here

Excel 2007 and higher files (.xlsx):

If you are dealing with .xlsx Excel 2007 or later files, you cannot use the Jet engine, but you can use the ACE OLEDB driver:

  • ACE 32bit OLEDB driver if 32bit Office is installed:
    • Provider := Microsoft.ACE.OLEDB.12.0;Data Source="C:\temp\MyExcel.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES;“;Jet OLEDB:Database Password=””;
  •  ACE 64bit OLEDB driver if 64bit Office installed and 64bit ACE installed:
    • Provider := Microsoft.ACE.OLEDB.14.0;Data Source="C:\temp\MyExcel.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES;“;Jet OLEDB:Database Password=””;
  • NB. YES you read correctly, that is Jet OLEDB:Database Password=””; and NOT ACE OLEDB:Database Password=””; !!! AND strangely enough, you can connect to a password protected file without supplying a password at all BUT, whether or not you supply a password, the password protected Excel file MUST be already open in full edit mode within Excel first!
  • NB. to install ACE 64bit on a machine which already has ACE 32bit installed, use command line: AccessDatabaseEngine_X64.exe /passive
  • to connect to Excel 97-2003 .xls via ACE use Extended Properties=”Excel 8.0;HDR=YES;”
  • to connect to Excel 2007-2010 .xlsx via ACE use Extended Properties=”Excel 12.0 Xml;HDR=YES;”
  • to connect to Excel 2007-2010 .xlsm via ACE use Extended Properties=”Excel 12.0 Macro;HDR=YES;”
  • to connect to Excel 2007-2010 .xlsb via ACE use Extended Properties=”Excel 12.0;HDR=YES;”
  • if you get the error “could not find installable ISAM”, you have probably made an error with the connection string.
  • unfortunately, if the Excel file is password protected to read, you will get an error “External table is not in the expected format” unless it is already opened in Excel and user has edit rights to the spreadsheet! So we have the same old situation as with the Jet OLEDB provider!
  • and don’t bother trying Password=”xxx”;User ID=Admin; as additional parameters, because as soon as you add a value for password, you get the same old Jet issue of “The workgroup information file is missing or opened exclusively by another user”.