Access and Excel

[Jeff]

Hi -

I'm reposting this question because I never got a reply to my earlier post.
And since I'm an MSDN subscriber ....

I have an Access application that opens and reads Excel workbooks.

The workbooks are formatted and contain data returned by remote users. A
few users seem to be turning Workbook Sharing on, so (for these users) the
'Allow changes by more than one user at the same time' checkbox in Tools ->
Share Workbook is checked.

When the Access app opens the affected workbook, I get a "Runtime error
'1004': Application-defined or object-defined error". This doesn't happen
when the Access app opens the same workbooks from users who don't turn
Workbook Sharing on. And the problem doesn't occur if I first manually
check
each workbook and turn sharing off.

This, however, needs to be an automated process. How can my Access VBA code
turn off Workbook sharing on a workbook so it will open without error?

Thanks for your help.

- Jeff

 

[Karl E. Peterson]

I'm reposting this question because I never got a reply to my earlier
post.

Sorry, I can't help there, either, but...

And since I'm an MSDN subscriber ....

Have you signed-up (registered) for Microsoft response?

http://msdn.microsoft.com/newsgroups/managed

Good luck (getting through *that* maze!)... Karl

 

[Jeff]

Yes, Karl, I have -

And I've gotten responses to other questions (posted in other newsgroups),
but this is my first post to this group. And I'd really appreciate a
solution to the problem that I posed.

- Jeff

 

[james.igoe]

What does your code look like?

 

[Jeff]

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):

ActiveSheet.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 ActiveSheet.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

 

[George Nicholson]

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 sheet, 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
ActiveSheet.Protect?
ActiveSheet.EnableSelection = xlUnlockedCells 'Only Unlocked cells
can be selected while protection is on
ActiveSheet.EnableSelection = xlLockedCells 'Only locked cells
can be selected
ActiveSheet.EnableSelection = xlNoSelection 'No cells can be
selected
ActiveSheet.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),