Dissable Workbook_Open Event in certian cases

[Michael]

I have a MS Access routine that opens a whole bunch of Excel models
and sucks data out into a data base. Problem is since I set it up some
of the excel files now have a Workbook_Open event that gets triggered
when Access Opens the workbooks. The event asks for some user input
which I want to avoid when opened by Access.

Is there a way for Excel to tell that it is being opend by VBA in
Access as opposed to the user opening the file?

This is how I am opening the workbooks from Excel
Set xlsApp = CreateObject("Excel.application")
Set wb = xlsApp.Workbooks.Open(Path & Filename, , True)

Any ideas would be appreciated.

Thanks.

 

[AB]

I'm not sure if it's possible - as in Excel there is no 'shift' (to
override startup) as in Access, although I might be wrong.

For what's my 0.02$ worth:
- if your access is just getting data from Excel and not putting
anything in back - then i don't think you need to open them up - just
link/import the excel data without even opening them up;

- if you indeed need to open them up you could modify your excel file
this way:
-- have the Workbook_Open routine do nothing but launch another
routine 'ontime' - like 2 seconds after opening the file. The OnTime
routine would do what previously the Workbook_Open used to do
-- add a custom property to your Wrokbook - something like Public
NotRunOnTime As Boolean (it would default to False)
-- have the OnTime routine check NotRunOnTime property and exit if
TRUE
-- when opening the wb from Access - the Workbook_Open will set the
2 sec timer for the OnTime to run but within the 2 sec you set the new
property (NotRunOnTime) to TRUE controling the .xls from Access. Then
after the 2 sec OnTime will see that NotRunOnTime=TRUE and will exit
not launching the whole code . Make sure to close the wb at the end
and not save the NotRunOnTime=true).

Just brainstorming but maybe will give you some ideas.

 

[Michael]

That seems like a workaround that might work but I was hoping to find
something that could tell the difference between a user opening it and
Access opening it? Is there a way to distiguish? If so then I just add
an if statement to On Open event.

 

[Paul C]

Within Excel and Excel VBA

Application.EnableEvents=false will suppress the event triggers and
Application.EnableEvents=true will turn them back on.

Since you are starting in Access you may have to do some fiddling to get
Access code to suppress Excel events. I am not if this line in Access code
will suppress the Excel event and I am not sure of the exact syntax to cross
applications.

Something like xlsApp.EnableEvent=false may work.

Hope this at least points you in the right direction.

 

[Michael]

Except for the missing s end the end of EnableEvents, which took me a
minute to figure out, this worked perfectly.

Thank you very muchl