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
sheetnames.<g

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" wrote in message
...
Robert Crandal submitted this idea :

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

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc