Disabling Events

[MWE]

I have a VBA procedure that runs in Outlook. It opens an Excel
spreadsheet (A.xls), reads some data from sheet "B" and then closes
A.xls There are macros in A.xls and in my Personal.xls that will
execute on open. I do not want that to happen when A.xls is opened
from Outlook. I have tried using the EventsEnable = False (which works
when one spreadsheet is opening another), but that does not seem to
work here. The problem is not the macros in A.xls but the macros in
Personal.xls. One of those macros does a lot of fetching from yet
other spreadsheets, build menus, and other stuff. If that macro
executes, it seems to get confused and generate a compiler error. The
Outlook procedure still runs correctly to completion but I would like
to eliminate the error. Any help would be appreciated. The relevant
VBA code (in Outlook) is below.

Thanks


'
' define Excel types
'
Dim xLApp As Excel.Application
Dim xLBook As Excel.Workbook
Dim xLSheet As Excel.Worksheet
'
' traditional definitions
'
Dim xLPath As String
Dim xLFileName As String
'
' define location and filename of target spreadsheet
'
xLPath = "D:\TestArea"
xLFileName = "A.xls"
'
' define application object
' set events to false (so macros do not run on startup)
'
Set xLApp = CreateObject("Excel.Application")
xLApp.EnableEvents = False
'
' open target spreadsheet
' define target worksheet
'
xLApp.Workbooks.Open FileName:=xLPath + "\" + xLFileName
Set xLSheet = xLApp.Workbooks(xLFileName).Worksheets("B")
'
' perform spreadsheet operations
'
... do various things ...

'
' close spreadsheet
' misc housekeeping
'
xLApp.Workbooks(xLFileName).Close

 

[Jim Rech]

I believe automation and enableevents do not work as expected, at least with
Excel 2000:

http://support.microsoft.com/default.aspx?scid=kb;en-us;211626&Product=xlw

As a workaround can your code close Personal before opening the other
workbook?

 

[MWE]

Jim: thanks for your reply.

You suggested: "As a workaround can your code close Personal before
opening the other workbook"

I do not know what this means. Please clarify. Approaches I have
considered:

1. disabling the execution of any macros in A.xls or Personal.xls
That is what I was trying to do with EnableEvents

2. programmatically mod the code lines in Personal.xls that call the
problem-creating procedure before I open A.xls and then restoring the
code after I close A.xls I know how to do that, it just seems ugly.

3. programmatically move Personal.xls to another folder before opening
A.xls and then moving it back after I close A.xls Also ugly

4. bypassing the calls in Personal!Workbook_Open that create the
problem by testing what other workbooks are open. Unfortunately,
Personal.xls opens before anything else so the only WB it can see is
itself

Thanks

MWE

 

[Jim Rech]

If Personal.xls is causing a problem when another workbook is opened (I'm
taking your word that it is so) then close it.

Set xLApp = CreateObject("Excel.Application")
xLApp.Workbooks("Personal.xls").Close, False
xLApp.Workbooks.Open FileName:=xLPath + "\" + xLFileName

 

[MWE]

Jim: thanks (again).

Personal.xls is not causing the problem when another spreadsheet i
opened. Rather Personal.xls is causing the problem when it opens.
That may not have been very clear, but I did pretty much state that i
considerations #2, #3 and #4.

On the off chance that I did not understand the sequence of events,
tried the extra line of code you suggested. It did not work.

At this point, option #3 is the easiest.


MW