Hi Peter
On 8 Apr, 23:23, "Peter T" <peter_t@discussions wrote:
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


Regards,
Peter T

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- Hide quoted text -

- Show quoted text -