Trapping/Answering MsgBox When Workbook Opened From Access?

[LarryP]

I have a number of Excel source files that contain
autoexec code to tell the user the age of the file and ask
him to confirm that he still wants to open it. This is
required when the user opens the file directly, but I
sometimes open these files programmatically from Access.
I would like to "turn off" this feature in that case so
the MsgBoxes don't stop the process repeatedly pending
a "Yes" from the user. Anybody know some kind of If test
that can be added to the Excel autoexec VBA to say "do
this only if a real person opens the file; if an external
program opens it, don't"?
(I also posted this problem to the Access group in case it
can be attacked from that end.)

 

[Juan Pablo González]

Disable events in Excel before opening the workbook, re-enable after.

oExcelApp.EnableEvents = False
'open book here
oExcelApp.EnableEvents = True

 

[Bob Phillips]

Have you put this code in the Workbook_Open event? If so, put it in an
Auto_Open macro instead.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[LarryP]

Well, actually it exists in two places. It's in the
Workbook_Open event, and also as the first step in a user-
launched macro that populates the workbook with
information from another, even more deeply buried source
file. Don't know how to set up at Auto-Open macro;
thought that's what Workbook_Open is. Couldn't find
Auto_Open or anything that resembles it in the Excel
Help. Bear in mind, though, that wherever I put it, I
still need a way to say "do this if a regular user wants
to open the file, but don't do it if an external program
wants to open the file." Don't know if such an animal
exists.

PS -- What's a Purbeck? This doesn't have anything to do
with nudist neighbors and a Zeiss telescope, does it?

 

[LarryP]

I'm tinkering with this; looks like it may get me a step
closer to the answer, for which I thank you. However,
what I'm still going to need is a way to turn this on and
off, i.e., disable it if an external program opens the
file, but leave it enabled if a "regular user" opens it.
A thought: when Excel file opens, is it given a
username? If so, is there some way I can send a specific
fake username from Access and use that as the If-Then
criterion?

 

[Bob Phillips]

Larry,

An Auto_Open macro is one that is automatically run when the workbook is
opened in Excel just like Workbook_Open, but is not run when the workbook is
opened via automation. It simply goes into a standard code module, it is
just the name that is special.

And yes, that's the beach at Stud at the foot of the Purbecks.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[LarryP]

Hmmm -- sounds like a potential solution. Thanks, I'll
experiment with it.

And may your telescope have good resolution.

 

[LarryP]

Auto_Open looks like it's going to be the Holy Grail. I
wrote it both as a Sub and a Function, slightly different
implementation in my internal sub, same satisfactory
result. One oddity, though: when I call it from the
internal sub (as opposed to running it automatically on
file open) it basically runs fine, but throws a Type 13
(type mismatch) error. I got around that by error
trapping to ignore any Type 13's, but am not really
comfortable with that. Any idea what's generating this
error and how to resolve it other than sticking my head in
the sand via error-trapping?

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Show us the code Larry.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)