Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dissable Workbook_Open Event in certian cases
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dissable Workbook_Open Event in certian cases
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. On Dec 3, 7:57*pm, Michael wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dissable Workbook_Open Event in certian cases
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dissable Workbook_Open Event in certian cases
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. -- If this helps, please remember to click yes. "Michael" wrote: 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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dissable Workbook_Open Event in certian cases
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_open Event | Excel Programming | |||
Workbook_Open() Event | Excel Discussion (Misc queries) | |||
Workbook_open Event | Excel Programming | |||
Workbook_Open Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |