{"id":553,"date":"2012-03-11T16:57:23","date_gmt":"2012-03-11T06:57:23","guid":{"rendered":"http:\/\/www.desiderata.com.au\/blog\/?p=553"},"modified":"2012-04-26T18:38:48","modified_gmt":"2012-04-26T08:38:48","slug":"connecting-to-a-password-protected-microsoft-excel-spreadsheet-using-delphi-tadoconnection","status":"publish","type":"post","link":"http:\/\/www.desiderata.com.au\/blog\/?p=553","title":{"rendered":"Connecting to a password-protected Microsoft Excel spreadsheet using Delphi TADOConnection"},"content":{"rendered":"

There is a little trick to connecting to a password protected .xls Excel spreadsheet using the Jet 4.0 OLEDB driver.<\/p>\n

 <\/p>\n

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”.<\/p>\n

 <\/p>\n

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”.<\/p>\n

 <\/p>\n

So here is the Connection String which will open the connection successfully in Read Only mode:<\/p>\n

 <\/p>\n

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

 <\/p>\n

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.<\/p>\n

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!<\/p>\n

This is not only an issue in Delphi but in any application that uses Jet 4.0 OLEDB engine.<\/p>\n

Of course, another solution is to use COM OLE automation such as:<\/strong><\/p>\n