In Excel 2007 how to switch off macro with a macro

[Guest]

Spreadsheet "A" runs a macro that opens spreadsheet "B". It gets data and
then closes "B" without saving (other spread sheets access "B" and deposit
data). A, then uses data for report.

However, when "B" opens it runs its own macros (the code doesn't ask it to
do this) and this creates a mess.

 

[Otto Moehrbach]

Micky
Apparently, Workbook B has code that runs automatically when that file
is opened. What you have to do is either change that code so that it does
you no harm or delete it. Post back if you need more. HTH Otto

 

[Greg Lovern]

If B's macros are being run from B's Workbook_Open event, then use
Application.EnableEvents:

Dim bEnableEvents as Boolean
bEnableEvents = Application.EnableEvents
Application.EnableEvents = False
'<open B here>
Application.EnableEvents = bEnableEvents

But that won't help if B's macros are being run from an auto_open
macro. In that case, since you're only reading data, you could leave B
closed and read the data from A either with formulas or with an ODBC
query.

Another option if you're in charge of the other workbooks' macros:

From the macros in the other workbooks that write to B, or in B's

Workbook_BeforeClose event, save out text files with the data. Then
open those text files in A instead of opening B.


Greg Lovern
http://PrecisionCalc.com