Stop Macros from Running When Workbook Opened via Automation

"MyFileI have an excel workbook that when opened by the user runs a macro
from the Workbook.OnOpen event. This then displays a VB form for the user to
enter some data.

I need to open this workbook using VBA from Access, add some data into the
cells on Sheet1, save and close the workbook and then email it to the user.

The problem is that when I use WorkBooks.Open("MyFileName") the code in the
Workbook.OnOpen event takes over and displays the form.

Is there any way I can stop all code in the workbook from running when I
open it using WorkBooks.Open("MyFileName"). The Macro security feature does
not apply in this case as this method seems to ignore the setting anyway.

Please help as I have reached a dead end.
Kind regards
Tim

You're opening an instance of excel and opening your workbook?

Maybe something like:

Dim XLApp As Object
Dim XLWkbk As Object

Set XLApp = CreateObject("Excel.Application")

xlapp.visible = true 'nice for testing
xlapp.enableevents = false 'stop workbook_open event

set xlwkbk = xlapp.workbooks.open("c:\myfolder1\myfolder2\test.xls")

'do your work

xlwkbk.close savechanges:=false

xlapp.enableevents = true

set xlwkbk = nothing
xlapp.quit
set xlapp = nothing


Google Boy of Company C wrote:
>
> "MyFileI have an excel workbook that when opened by the user runs a macro
> from the Workbook.OnOpen event. This then displays a VB form for the user to
> enter some data.
>
> I need to open this workbook using VBA from Access, add some data into the
> cells on Sheet1, save and close the workbook and then email it to the user.
>
> The problem is that when I use WorkBooks.Open("MyFileName") the code in the
> Workbook.OnOpen event takes over and displays the form.
>
> Is there any way I can stop all code in the workbook from running when I
> open it using WorkBooks.Open("MyFileName"). The Macro security feature does
> not apply in this case as this method seems to ignore the setting anyway.
>
> Please help as I have reached a dead end.
> Kind regards
> Tim

--

Dave Peterson

Hi Dave

Thanks for the reply.

I had come across enableevents before and tried it but it didn't seem to
work. I have found another solution for now by using Auto_Open() which only
runs when the workbook is opened in the normal way.

I will have another go with enableevents when I have more time to experiment.

Many thanks
Tim

"Dave Peterson" wrote:

> You're opening an instance of excel and opening your workbook?
>
> Maybe something like:
>
> Dim XLApp As Object
> Dim XLWkbk As Object
>
> Set XLApp = CreateObject("Excel.Application")
>
> xlapp.visible = true 'nice for testing
> xlapp.enableevents = false 'stop workbook_open event
>
> set xlwkbk = xlapp.workbooks.open("c:\myfolder1\myfolder2\test.xls")
>
> 'do your work
>
> xlwkbk.close savechanges:=false
>
> xlapp.enableevents = true
>
> set xlwkbk = nothing
> xlapp.quit
> set xlapp = nothing
>
>
> Google Boy of Company C wrote:
> >
> > "MyFileI have an excel workbook that when opened by the user runs a macro
> > from the Workbook.OnOpen event. This then displays a VB form for the user to
> > enter some data.
> >
> > I need to open this workbook using VBA from Access, add some data into the
> > cells on Sheet1, save and close the workbook and then email it to the user.
> >
> > The problem is that when I use WorkBooks.Open("MyFileName") the code in the
> > Workbook.OnOpen event takes over and displays the form.
> >
> > Is there any way I can stop all code in the workbook from running when I
> > open it using WorkBooks.Open("MyFileName"). The Macro security feature does
> > not apply in this case as this method seems to ignore the setting anyway.
> >
> > Please help as I have reached a dead end.
> > Kind regards
> > Tim
>
> --
>
> Dave Peterson
>

Hi Dave

I have found time to do some further testing on this. Using EnableEvents =
False does not stop the code from running, whereas using Auto_Open() instead
of Workbook_Open() solves my problem.

From further reading it would seem that Auto_Open() originates from older
versions of Excel and has only been left in for compatability. Should
Microsoft remove it in future I might be stuffed.

Kind regards
Tim ffitch

"Dave Peterson" wrote:

> You're opening an instance of excel and opening your workbook?
>
> Maybe something like:
>
> Dim XLApp As Object
> Dim XLWkbk As Object
>
> Set XLApp = CreateObject("Excel.Application")
>
> xlapp.visible = true 'nice for testing
> xlapp.enableevents = false 'stop workbook_open event
>
> set xlwkbk = xlapp.workbooks.open("c:\myfolder1\myfolder2\test.xls")
>
> 'do your work
>
> xlwkbk.close savechanges:=false
>
> xlapp.enableevents = true
>
> set xlwkbk = nothing
> xlapp.quit
> set xlapp = nothing
>
>
> Google Boy of Company C wrote:
> >
> > "MyFileI have an excel workbook that when opened by the user runs a macro
> > from the Workbook.OnOpen event. This then displays a VB form for the user to
> > enter some data.
> >
> > I need to open this workbook using VBA from Access, add some data into the
> > cells on Sheet1, save and close the workbook and then email it to the user.
> >
> > The problem is that when I use WorkBooks.Open("MyFileName") the code in the
> > Workbook.OnOpen event takes over and displays the form.
> >
> > Is there any way I can stop all code in the workbook from running when I
> > open it using WorkBooks.Open("MyFileName"). The Macro security feature does
> > not apply in this case as this method seems to ignore the setting anyway.
> >
> > Please help as I have reached a dead end.
> > Kind regards
> > Tim
>
> --
>
> Dave Peterson
>

If what you need is to disable the events try with

Application.Enableevents=True/False

Are you sure you properly qualified EnableEvents as Dave showed?

Don't worry about Auto_Open, it is far too widespread for MS to remove it.
It will probably disappear in a version where backward compatibility is
completely lost.

--
HTH

Bob Phillips

"Google Boy of Company C" <[email protected]>
wrote in message news:[email protected]...
> Hi Dave
>
> I have found time to do some further testing on this. Using EnableEvents =
> False does not stop the code from running, whereas using Auto_Open()
instead
> of Workbook_Open() solves my problem.
>
> From further reading it would seem that Auto_Open() originates from older
> versions of Excel and has only been left in for compatability. Should
> Microsoft remove it in future I might be stuffed.
>
> Kind regards
> Tim ffitch
>
> "Dave Peterson" wrote:
>
> > You're opening an instance of excel and opening your workbook?
> >
> > Maybe something like:
> >
> > Dim XLApp As Object
> > Dim XLWkbk As Object
> >
> > Set XLApp = CreateObject("Excel.Application")
> >
> > xlapp.visible = true 'nice for testing
> > xlapp.enableevents = false 'stop workbook_open event
> >
> > set xlwkbk = xlapp.workbooks.open("c:\myfolder1\myfolder2\test.xls")
> >
> > 'do your work
> >
> > xlwkbk.close savechanges:=false
> >
> > xlapp.enableevents = true
> >
> > set xlwkbk = nothing
> > xlapp.quit
> > set xlapp = nothing
> >
> >
> > Google Boy of Company C wrote:
> > >
> > > "MyFileI have an excel workbook that when opened by the user runs a
macro
> > > from the Workbook.OnOpen event. This then displays a VB form for the
user to
> > > enter some data.
> > >
> > > I need to open this workbook using VBA from Access, add some data into
the
> > > cells on Sheet1, save and close the workbook and then email it to the
user.
> > >
> > > The problem is that when I use WorkBooks.Open("MyFileName") the code
in the
> > > Workbook.OnOpen event takes over and displays the form.
> > >
> > > Is there any way I can stop all code in the workbook from running when
I
> > > open it using WorkBooks.Open("MyFileName"). The Macro security feature
does
> > > not apply in this case as this method seems to ignore the setting
anyway.
> > >
> > > Please help as I have reached a dead end.
> > > Kind regards
> > > Tim
> >
> > --
> >
> > Dave Peterson
> >

Hi Bob

Yes, my code was identical and yet it seemed to ignore the
xlapp.enableevents = false

Kind regards
Tim ffitch

"Bob Phillips" wrote:

> Are you sure you properly qualified EnableEvents as Dave showed?
>
> Don't worry about Auto_Open, it is far too widespread for MS to remove it.
> It will probably disappear in a version where backward compatibility is
> completely lost.
>
> --
> HTH
>
> Bob Phillips
>
> "Google Boy of Company C" <[email protected]>
> wrote in message news:[email protected]...
> > Hi Dave
> >
> > I have found time to do some further testing on this. Using EnableEvents =
> > False does not stop the code from running, whereas using Auto_Open()
> instead
> > of Workbook_Open() solves my problem.
> >
> > From further reading it would seem that Auto_Open() originates from older
> > versions of Excel and has only been left in for compatability. Should
> > Microsoft remove it in future I might be stuffed.
> >
> > Kind regards
> > Tim ffitch
> >
> > "Dave Peterson" wrote:
> >
> > > You're opening an instance of excel and opening your workbook?
> > >
> > > Maybe something like:
> > >
> > > Dim XLApp As Object
> > > Dim XLWkbk As Object
> > >
> > > Set XLApp = CreateObject("Excel.Application")
> > >
> > > xlapp.visible = true 'nice for testing
> > > xlapp.enableevents = false 'stop workbook_open event
> > >
> > > set xlwkbk = xlapp.workbooks.open("c:\myfolder1\myfolder2\test.xls")
> > >
> > > 'do your work
> > >
> > > xlwkbk.close savechanges:=false
> > >
> > > xlapp.enableevents = true
> > >
> > > set xlwkbk = nothing
> > > xlapp.quit
> > > set xlapp = nothing
> > >
> > >
> > > Google Boy of Company C wrote:
> > > >
> > > > "MyFileI have an excel workbook that when opened by the user runs a
> macro
> > > > from the Workbook.OnOpen event. This then displays a VB form for the
> user to
> > > > enter some data.
> > > >
> > > > I need to open this workbook using VBA from Access, add some data into
> the
> > > > cells on Sheet1, save and close the workbook and then email it to the
> user.
> > > >
> > > > The problem is that when I use WorkBooks.Open("MyFileName") the code
> in the
> > > > Workbook.OnOpen event takes over and displays the form.
> > > >
> > > > Is there any way I can stop all code in the workbook from running when
> I
> > > > open it using WorkBooks.Open("MyFileName"). The Macro security feature
> does
> > > > not apply in this case as this method seems to ignore the setting
> anyway.
> > > >
> > > > Please help as I have reached a dead end.
> > > > Kind regards
> > > > Tim
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>