Disabling macros when opening 2nd xl sheet from xl 1

[shavenlunatic]

Using MSO2003 on WinXP

I have an excel app which opens a 2nd excel sheet to gather information in it, which is fine, but when I close the 2nd sheet it has a macro build in to pop up a msgbox and ask if anything has been changed, if it has, it runs a bit of script. Now, everything halts when this message pops up and causes the original app to fall over.

I need to disable the macros in the excel file I am calling, i have tried "xlapp.enableevents = false" but to no avail. Can't seem to find anything else that will help me, any ideas?

Thanks in adv.

________
clueless

 

[combo]

If you work in excel xp or above, use:
Application.AutomationSecurity = msoAutomationSecurityForceDisable
or:
Application.AutomationSecurity = msoAutomationSecurityByUI
before opening the file

combo

 

[shavenlunatic]

nope. still pops up the form that the macro in the 2nd workbook.

Was confident that would work too

________
clueless

 

[combo]

How is the second workbook opened?
If by code, then:
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Workbooks.Open...
should lock auto macros (in fact, it locks all macros and is equivalent to disable macros in securitydialog on opening file).
If the second workbook is opened as referenced vba project, modify code in the second workbook.

Does the message box come from the second workbook or it is standard excel message before closing changed workbook and is not caused by macro? In the second case either close workbook with code with SaveChanges parameter set to false, or trap beforeclose/beforesave events of the secont workbook in your excel application, using WithEvents declared variable. The second option is not elementary, but fully available in excel vba without api, will allow to control required events.

combo

 

[sahmiele]

in the macro in the second workbook that runs when you close the file, put in some code to see if your first file is open. write an "if/then" statement to skip the code you want to avoid if it is open.