How to get value of a excel-fields without open the workbook.

[Pat]

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

[Amedee Van Gasse]

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

[Jamie Collins]

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.

--

[Amedee Van Gasse]

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

[Jamie Collins]

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.

--