Pat shared this with us in microsoft.public.excel.programming:

Hi,

I try to via vba-code to get fields value of a Excel-workbook without
open. It seem to work. But when i try to do it with an
Excel-workbook it is protect by a password, it doesn't work anymore.
Because i don't want to open the workbook first and close it against.
In case i want to generate a summary of 500 workbooks, and need to
open all the workbook one for one, then it affect the performance.

Hope that some people can help me!

Best regards,

Pat

I assume you are familiar with this page?
http://www.rondebruin.nl/ado.htm

I suppose the password should go somewhere in the connection string.
That's where you have to put it when connecting to an SQL or LDAP
server, in my experience. Alas, I wouldn't know the syntax for Excel.

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way
http://www.catb.org/~esr/faqs/smart-questions.html
How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoyn...-with-yes-or-n
o-answers.html

Amedee Van Gasse wrote:
when i try to do it with an
Excel-workbook it is protect by a password, it doesn't work
anymore.

I assume you are familiar with this page?
http://www.rondebruin.nl/ado.htm

I suppose the password should go somewhere in the connection string.

I assume you are not familiar with this page:

XL2000: "Could Not Decrypt File" Error with Password Protected File
http://support.microsoft.com/?KBID=211378

A workbook which requires a password to open cannot be read using ADO.

Jamie.

--

Jamie Collins shared this with us in microsoft.public.excel.programming:


Amedee Van Gasse wrote:
when i try to do it with an
Excel-workbook it is protect by a password, it doesn't work
anymore.

I assume you are familiar with this page?
http://www.rondebruin.nl/ado.htm

I suppose the password should go somewhere in the connection string.

I assume you are not familiar with this page:

XL2000: "Could Not Decrypt File" Error with Password Protected File
http://support.microsoft.com/?KBID=211378

A workbook which requires a password to open cannot be read using ADO.

Jamie.

--

One is never too old to learn.

<rant
Bleh. That really sucks. Why can't we just add the damn password to the
connection string as with *normal* data sources. Someone at Redmont was
probably thinking, "hey, let's see how we can annoy our users".
</rant

--
Amedee Van Gasse using XanaNews 1.17.3.1
If it has an "X" in the name, it must be Linux?

How To Ask Questions The Smart Way
http://www.catb.org/~esr/faqs/smart-questions.html
How to Report Bugs Effectively
http://www.chiark.greenend.org.uk/~sgtatham/bugs.html
Only ask questions with yes/no answers if you want "yes" or "no" as the
answer.
http://homepages.tesco.net/~J.deBoyn...-with-yes-or-n
o-answers.html

Amedee Van Gasse wrote:
A workbook which requires a password to open cannot be read using
ADO.

<rant
Bleh. That really sucks. Why can't we just add the damn password to
the
connection string as with *normal* data sources. Someone at Redmont
was
probably thinking, "hey, let's see how we can annoy our users".
</rant

IIRC, unlike regular database security, adding a workbook password
encrypts the data on disk and ADO doesn't have the capability to
decrypt it.

FWIW a worksheet password does not encrypt. Here are the details in
full and, as this is about ADO, how about some DDL:

CREATE TABLE XLProtectionPermissions (
ExcelObject VARCHAR(5)
DEFAULT 'Sheet' NOT NULL,
HasPassword VARCHAR(1)
DEFAULT 'N' NOT NULL,
CanRead VARCHAR(1)
DEFAULT 'N' NOT NULL,
CanWrite VARCHAR(1)
DEFAULT 'N' NOT NULL,
PRIMARY KEY (ExcelObject, HasPassword),
CHECK (ExcelObject IN ('Sheet', 'Book')),
CHECK (HasPassword IN ('N', 'Y')),
CHECK (CanRead IN ('N', 'Y')),
CHECK (CanWrite IN ('N', 'Y'))
)
;
INSERT INTO XLProtectionPermissions
(ExcelObject, HasPassword, CanRead, CanWrite)
VALUES ('Sheet', 'N', 'Y', 'Y')
;
INSERT INTO XLProtectionPermissions
(ExcelObject, HasPassword, CanRead, CanWrite)
VALUES ('Sheet', 'Y', 'Y', 'N')
;
INSERT INTO XLProtectionPermissions
(ExcelObject, HasPassword, CanRead, CanWrite)
VALUES ('Book', 'N', 'Y', 'Y')
;
INSERT INTO XLProtectionPermissions
(ExcelObject, HasPassword, CanRead, CanWrite)
VALUES ('Book', 'Y', 'N', 'N')
;
SELECT
ExcelObject, HasPassword, CanRead, CanWrite
FROM XLProtectionPermissions
;

Jamie.

--