Re: Access and Excel

Educated Guesswork:
1) The error isn't being raised when you open the books in question, but
after you start to "read" it (this is a bit unclear in your post)
2) The workbook has protected sheets, with protected cells (as you state)
3) When protecting a ***, it is possible to specify that certain cells
can't even be selected by the user (XL 2000 & later).
Is there an additional line of code like one of the following after
Active***.Protect?
Active***.EnableSelection = xlUnlockedCells 'Only Unlocked cells
can be selected while protection is on
Active***.EnableSelection = xlLockedCells 'Only locked cells
can be selected
Active***.EnableSelection = xlNoSelection 'No cells can be
selected
Active***.EnableSelection = xlNoRestrictions 'The default. any cell
can be selected

If any of the 1st 3 lines exist and your code tries to select an
"unselectable" type of cell while reading data, you will generate a 1004
since that action isn't currently permissable.

Suggestion1: open book, turn off protection, read the data, turn protection
back on (including EnableSelection), close the book.

Suggestion2: rewrite your code using methods to read data that won't trigger
a 1004 if a cell is unselectable. Maybe using Offset()? Not 100% sure that
there is a way within Excel. I can't remember if I've ever tried to work
around it myself, but I have a strong suspicion that it is possible, and I
think that may be based on experience.

Suggestion3: Don't use Excel automation, since these "protection" settings
only apply to the data when opened via Excel. Maybe import the XL files
directly into an Access temp table and do your thing? If nothing else you
could always get to it via ODBC.

HTH (& good luck),
--
George Nicholson

Remove 'Junk' from return address.


"Jeff" <jeff_nospam@xxxxxxxxxxxxxxx> wrote in message
news:wEIxe.2711$aY6.445@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> The 'culprit' seems to be in the Excel code. The workbook has one or more
> protected cells in it. And the following line is in the Sub workbook_open
> of the ThisWorkbook module (in Excel VBA):
>
> Active***.Protect DrawingObjects:=False, Contents:=True,
> Scenarios:=True
>
> If I open the workbook in Excel and the workbook is not shared, it opens
> without error. If I open it in Excel and it IS shared, I get:
>
> Run-time error '1004':
> Application-defined or object-defined error
>
> The error occurs independent of what I have in my Access app (in other
> words, even when I'm just opening the workbook from within Excel). But I
> need to be able to open such workbooks from my Access app without error.
> All suggestions (and/or explanations of what's going on) are welcome.
>
> I suppose I can just remove the Active***.Protect line. Does anyone see
> a
> downside to doing that? But I'd still like to understand why the error is
> raised with that line of code present.
>
> Thanks for your help.
>
> - Jeff
>
>
> <james.igoe@xxxxxxxxx> wrote in message
> news:1120310375.493921.273050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>
>> What does your code look like?
>>
>
>


.