Can't disable macros in 2nd Excel instance

[ic2]

I'm trying to write a routine in a "control" workbook" which will
modify an existing workbook without it being visible.

If I open the file within the existing Excel instance,
Application.Visible=False also hides the "control" workbook.

If I create a second instance of Excel and open the existing workbook
in that, I can't stop the macros from running in that workbook.

My code is:

Sub Macro2()
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
..EnableEvents = False
..Workbooks.Open Filename:= _
"C:\Documents and Settings\ianc\My Documents\Template Prep\II
checklist.xlt", _
UpdateLinks:=0
'
' More code here
'
..EnableEvents = True
End With
End Sub

The .EnableEvents = False should stop the macros in II Checklist.xlt
from running, but it doesn't.

If I open II Checklist.xlt in the existing Excel instance, the
EnableEvents command works as expected, but the Visible command also
hides the "control" workbook

Sub Macro2()
Application.Visible =False
Application.EnableEvents = False
Workbooks.Open Filename:= _
etc.

Any ideas where I'm going wrong.

Ian

 

[Jim Cone]

Ian,
Put the checklist.xlt, Workbook_Open code in a standard module in
an Autpen sub.
Autpen code does not run when a workbook is opened with Visual Basic,
unless the RunAutoMacros method is used.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
I'm trying to write a routine in a "control" workbook" which will
modify an existing workbook without it being visible.

If I open the file within the existing Excel instance,
Application.Visible=False also hides the "control" workbook.
If I create a second instance of Excel and open the existing workbook
in that, I can't stop the macros from running in that workbook.
My code is:

Sub Macro2()
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
With xlApp
..EnableEvents = False
..Workbooks.Open Filename:= _
"C:\Documents and Settings\ianc\My Documents\Template Prep\II
checklist.xlt", _
UpdateLinks:=0
'
' More code here
'
..EnableEvents = True
End With
End Sub

The .EnableEvents = False should stop the macros in II Checklist.xlt
from running, but it doesn't.
If I open II Checklist.xlt in the existing Excel instance, the
EnableEvents command works as expected, but the Visible command also
hides the "control" workbook
Sub Macro2()
Application.Visible =False
Application.EnableEvents = False
Workbooks.Open Filename:= _
etc.
Any ideas where I'm going wrong.
Ian

 

[ianc]

Thanks for the suggestion, Jim.

Ian,
Put the checklist.xlt, Workbook_Open code in a standard module in
an Autpen sub.

Nice idea in theory, but it won't work in practice. The idea of the
routine is to update remote Excel templates (II checklist.xlt in my
example). It sounds like I may have to resign myself to the workbook
being visible.

Just a thought. Is it possible to open a workbook minimised, or to
minimise/restore down immediately after opening?

It's a puzzle that EnableEvents is ineffective in the second Excel
instance, whereas other commands work fine

 

[Peter T]

It's very difficult to change the EnableEvents property of an automated
instance, normally will need to have an activeworkbook, probably the app
needs to be visible, and even then it may take a few attempts to get the
setting to change.

Why not open the file in your own instance. If necessary temporarily disable
screenupdating if you don't want it seen by the user.

Regards,
Peter T

 

[ianc]

Hi Peter

It's very difficult to change the EnableEvents property of an automated
instance, normally will need to have an activeworkbook, probably the app
needs to be visible, and even then it may take a few attempts to get the
setting to change.

This seems very odd. I should have thought that the response would be
consistent, even if it is necessary for the workbook to be active and
visible.

Why not open the file in your own instance. If necessary temporarily disable
screenupdating if you don't want it seen by the user.

I just tried this, but it didn't seem to work. I put the command just
before the workbook is opened, but that might be the wrong place. I
was hoping to just have a userform on screen advising te user what was
happening in the background, but it looks like that's not going to be
possible.

I think I'll just concentrate on the update function and let the
screen display take care of itself.

Ian