Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1081
  • Last Modified:

excel ADO connection string

Hi All,
when attempting to read data from and encrypted xlsx using:
dbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & SourceFile & "';Extended Properties=Excel 12.0;"
and dbConnection.Execute.

I get an error:
Error -2147467259, External table is not in the expected format. occurred in GetRecordsetFromClosedWorkbook

The error goes away when the file is not encrypted.

adding ;Database Password =test; to the connection string gives a different error:

Error -2147467259, Could not find installable ISAM. occurred in GetRecordsetFromClosedWorkbook

When I use Excel Open (with password) and copy/paste methods all is good

How does one add Excel encryption password to the ADO connection string?

  • 4
  • 3
2 Solutions
Password parameter does not help here but you may use the work around described here:
You just have to change JET.OLEDB for ACE.OLEDB etc.

Another possibility is to save decrypted workbook into a temporary file and then open it via ADO.

Yes, Microsoft could do it better...
COACHMAN99Author Commented:
Hi pcelba
thanks for the input. Unfortunately there are thousands of workbooks in this situation and inputting passwords at a prompt isn't an option. (and when I use with a password param, to read data, I am able to work without any prompts; just wanted to use ADO).
It is not necessary to ask user for the password but you may provide it as a parameter when opening the Excel workbook.

I don't know the exact syntax but you may use something like this:
Dim xl As New Excel.Application
Dim xwb As Excel.WorkBook

Set xwb = xl.Application.WorkBooks.Open "YourXLSXfile", , , , "password", "password"

and now you should be able to use ADODB connection without any restrictions.

I've tested FoxPro variant of above code and it opens password protected workbook without user intervention.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

COACHMAN99Author Commented:
I wasn't asking a question; just informing you of how I currently work around the password issue. (Workbooks.Open("L:\Backup\2014\PGT\BE.xlsx", , , , "test")
Yes, my English is not so good... you wrote "just wanted to use ADO" which I translated as you cannot use ADO even after opening the workbook via OLE automation...
COACHMAN99Author Commented:
1. One cannot use a password in an ADO connection to an encrypted Excel workbook.
2. Instead, one must use the Excel '' functionality (which makes use of the optional password).

thanks pcelba for input.
COACHMAN99Author Commented:
only workable option.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now