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

Ian,
Put the checklist.xlt, Workbook_Open code in a standard module in
an Auto_Open sub.
Auto_Open 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)



<(E-Mail Removed)>
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

Thanks for the suggestion, Jim.

On 8 Apr, 22:53, "Jim Cone" <jim.cone...@rcn.comXXX> wrote:
> Ian,
> Put the checklist.xlt, Workbook_Open code in a standard module in
> an Auto_Open 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

> Auto_Open code does not run when a workbook is opened with Visual Basic,
> unless the RunAutoMacros method is used.
> --
> Jim Cone
> San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware
> (Excel Add-ins / Excel Programming)
>
> <i...@ntlworld.com>
> 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

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



<(E-Mail Removed)> wrote in message
news:e94c49ba-f09d-4966-91ab-(E-Mail Removed)...
> 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

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
>
> <i...@ntlworld.com> wrote in message
>
> news:e94c49ba-f09d-4966-91ab-(E-Mail Removed)...
>
>
>
> > 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 -