ADO connection to a protected workbook?

[Robert Crandal]

I am making an ADO connection to macro enabled workbooks
(i.e, workbooks of type *.xlsm). Here is my connection string example:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data.xlsm;
Extended Properties=""Excel 12.0 Macro;HDR=NO;IMEX=1"""

So far, I have been able to make connections to very simple and basic
Excel 2007 macro files. However, when I try connecting to a workbook
that is password protected (with all sheets being password protected as
well),
then I CANNOT make a connection. I get an error message that reads
something like "Table is not in the expected format".

So, my question is, is ADO useless if the workbook is password protected?
This workbook also contains hidden sheets, so I wonder if that's the
problem?
What sort of things will prevent an ADO connection? I know I do have the
correct connection string, so something else must be wrong.

Thank you!

 

[GS]

Robert Crandal submitted this idea :

I am making an ADO connection to macro enabled workbooks
(i.e, workbooks of type *.xlsm). Here is my connection string example:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data.xlsm;
Extended Properties=""Excel 12.0 Macro;HDR=NO;IMEX=1"""

So far, I have been able to make connections to very simple and basic
Excel 2007 macro files. However, when I try connecting to a workbook
that is password protected (with all sheets being password protected as
well),
then I CANNOT make a connection. I get an error message that reads
something like "Table is not in the expected format".

So, my question is, is ADO useless if the workbook is password protected?
This workbook also contains hidden sheets, so I wonder if that's the problem?
What sort of things will prevent an ADO connection? I know I do have the
correct connection string, so something else must be wrong.

Thank you!

I'll have to do some looking into the password protected wkb issue.
I'll post my findings...

Meanwhile, (just a word of caution) though Jet/Ace are somewhat
forgiving and so let you get away without using the closing semi-colon
in front of your closing double quote, some other providers may not
allow that in your connection string. <IMO>It's just good practice to
always include it as a general rule.

"...;IMEX=1"";"

 

[Robert Crandal]

It turns out, the mistake was actually my own. My query string looked
like this:

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

The problem was, my workbook (which is protected), did not have
any sheets named "Sheet1", which probably explains why I got the error that
said "External table was not in the expected format.". Does that sound
right?

It therefore looks like I CAN make a connection to a password protected
sheet, I just need to reference actual sheet names, haha!

My bad!

 

[GS]

Robert Crandal laid this down on his screen :

It turns out, the mistake was actually my own. My query string looked
like this:

szSQL = "SELECT * FROM [Sheet1$A1:A1]"

The problem was, my workbook (which is protected), did not have
any sheets named "Sheet1", which probably explains why I got the error that
said "External table was not in the expected format.". Does that sound
right?

ADO error messages aren't the most intuitive for all scenarios, which
means they can be misleading. But yes, you must specify the sheetname
that contains the data you want. This precludes then that your
workbooks containing the data should be similar in structure for

It therefore looks like I CAN make a connection to a password protected
sheet, I just need to reference actual sheet names, haha!

My bad!

Here's what I got back from Rob Bovey regarding the open password
protection:

<From Rob Bovey>
There isn't any ADO solution for password protected workbooks unless
you
consider letting Excel open them first, which kind of defeats the
purpose of ADO even if it works technically. The contents of a password
protected workbook are encrypted by Excel and ADO has no way to decrypt
it.
</>

And yes, ADO works if sheet protection is in place as that doesn't
matter because that's only for the UI.