How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

I have an application which uses Word and Excel as automation servers. When
opening a document, I need to prevent macros from running automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False, True,
False)

What is the equivalent statement for Excel which I should be coding directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

use Private Sub Auto_Open in a general module instead of Private Sub
Workbook_Open() in thisworkbook.



--


Gary


"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers. When
opening a document, I need to prevent macros from running automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False, True,
False)

What is the equivalent statement for Excel which I should be coding directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Hi Gary,

I'm not sure what you are proposing. I don't have access to the internals of
the documents which I am opening. My code which I presented is from my
document handling application which is on the 'outside' of the documents.
I'm simply opening these document by using server instances of Word and
Excel via automation. I'm looking for a way to programmatically open these
documents, while at the same time circumventing any AutoRun (or AutoExecute)
macros which may be encoded into the documents.

Thanks,

- Joseph Geretz

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
use Private Sub Auto_Open in a general module instead of Private Sub
Workbook_Open() in thisworkbook.



--


Gary


"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True, False)

What is the equivalent statement for Excel which I should be coding
directly before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Set the AutomationSecurity property to msoAutomationSecurityForceDisable
before opening the file. Remember to set it back when you've finished. Use a
sequence something like --

Dim pWord As Word.Application
Dim pDoc As Word.Document
Dim pSetting As MsoAutomationSecurity

Set pWord = New Word.Application
pSetting = pWord.AutomationSecurity
pWord.AutomationSecurity = msoAutomationSecurityForceDisable
Set pDoc = pWord.Documents.Open([FileName])
pWord.AutomationSecurity = pSetting





"Joseph Geretz" wrote in message
...
Hi Gary,

I'm not sure what you are proposing. I don't have access to the internals
of the documents which I am opening. My code which I presented is from my
document handling application which is on the 'outside' of the documents.
I'm simply opening these document by using server instances of Word and
Excel via automation. I'm looking for a way to programmatically open these
documents, while at the same time circumventing any AutoRun (or
AutoExecute) macros which may be encoded into the documents.

Thanks,

- Joseph Geretz

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
use Private Sub Auto_Open in a general module instead of Private Sub
Workbook_Open() in thisworkbook.



--


Gary


"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True, False)

What is the equivalent statement for Excel which I should be coding
directly before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Joseph Geretz wrote...
I have an application which uses Word and Excel as automation servers. When
opening a document, I need to prevent macros from running automatically.
....

In Excel only two types of macros would run when opening a workbook
interactively - event handlers (not just Workbook_Open, but also
Calculate and SheetCalculate if there are any volatile functions called
in applicable cells) and the legacy Auto_Open macros. If you open a
workbook using VBA, Excel *won't* run its Auto_Open macros, but if
events are enabled, it will run event handlers in the opened workbook.
So disable event handlers prior to opening workbooks and enable them
afterwards.

On Error Resume Next
Application.EnableEvents = False
Workbooks.Open . . .
Application.EnableEvents = True
On Error Goto 0

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Hi Joseph,

I might be missing something in your question but with automation the auto
open/close events do not run unless you explicitly make them. In other words
nothing to do to prevent them running automatically. Put open/close events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When
opening a document, I need to prevent macros from running automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

I don't know about Excel, but Word ones do. Try it: add an AutoOpen macro to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the auto
open/close events do not run unless you explicitly make them. In other
words
nothing to do to prevent them running automatically. Put open/close events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When
opening a document, I need to prevent macros from running automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

I'm confused. You specifically asked about opening an Excel Workbook and
preventing it's open events from running. I tried to clarify that when using
automation they do not automatically fire. Excel does not have an equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup, such
as "checked" addins and files in the StartupPath (typically xlstart folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the
auto
open/close events do not run unless you explicitly make them. In other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

"Jezebel" wrote in message
...
Set the AutomationSecurity property to msoAutomationSecurityForceDisable
before opening the file. Remember to set it back when you've finished. Use
a sequence something like --

Dim pWord As Word.Application
Dim pDoc As Word.Document
Dim pSetting As MsoAutomationSecurity

Set pWord = New Word.Application
pSetting = pWord.AutomationSecurity
pWord.AutomationSecurity = msoAutomationSecurityForceDisable
Set pDoc = pWord.Documents.Open([FileName])
pWord.AutomationSecurity = pSetting

Jezebel,

You're answering the wrong question. You're describing how to open a Word
document from Excel. The question was how to open an Excel workbook from
Word.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

If using the TestOpen/Close demos with late binding and the code is say Word
would need to replace the xl constants as follows:

oWB.RunAutoMacros 1& ' xlAutoOpen

oWB.RunAutoMacros 2& ' xlAutoClose

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the auto
open/close events do not run unless you explicitly make them. In other
words
nothing to do to prevent them running automatically. Put open/close events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When
opening a document, I need to prevent macros from running automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

There seems to be some confusion here ..

When starting an *Application* (Word, Excel, etc.) via automation,
automacros do NOT run.

When opening a Word *Document* via automation, automacros DO run

It's slightly more complicated in Excel I believe in that Workbook Events DO
run but Auto_Open macros DON'T run.

The Application.AutomationSecurity setting applies, AFAIK, equally to Word
and Excel.

--
Enjoy,
Tony

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the
auto
open/close events do not run unless you explicitly make them. In other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook) and it
should do the trick.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Jezebel" wrote in message
...
Set the AutomationSecurity property to msoAutomationSecurityForceDisable
before opening the file. Remember to set it back when you've finished.
Use
a sequence something like --

Dim pWord As Word.Application
Dim pDoc As Word.Document
Dim pSetting As MsoAutomationSecurity

Set pWord = New Word.Application
pSetting = pWord.AutomationSecurity
pWord.AutomationSecurity = msoAutomationSecurityForceDisable
Set pDoc = pWord.Documents.Open([FileName])
pWord.AutomationSecurity = pSetting

Jezebel,

You're answering the wrong question. You're describing how to open a Word
document from Excel. The question was how to open an Excel workbook from
Word.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Oops, thought I was replying to a response from Joseph (OP) hence I was
confused, whereas of course I replied to a post from Jezebel who I think has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook and
preventing it's open events from running. I tried to clarify that when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup, such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the
auto
open/close events do not run unless you explicitly make them. In other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document), False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook) and it
should do the trick.

Depends on the version of Office being used. The AutomationSecurity property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property in all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or earlier,
macros are automatically trusted when documents are opened via automation.

Looking back over the thread, it's probably the wrong answer anyway. The
request was for an Excel equivalent of Word's WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly), setting
(Excel) Application.EnableEvents to False is probably what is wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook) and it
should do the trick.

Depends on the version of Office being used. The AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

While not exactly the same (the applications do work differenly), setting
(Excel) Application.EnableEvents to False is probably what is wanted.

In normal use disabling events would prevent the Workbook_Open() but would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with automation
of Excel neither of the open events are called without use of RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property in all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or earlier,
macros are automatically trusted when documents are opened via automation.

Looking back over the thread, it's probably the wrong answer anyway. The
request was for an Excel equivalent of Word's WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly), setting
(Excel) Application.EnableEvents to False is probably what is wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook) and it
should do the trick.

Depends on the version of Office being used. The AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

In my Excel 2003, the Workbook_Open Event runs when a Workbook is opened via
automation - but an Auto_Open macro doesn't. Yours apparenly does something
different so there must be some other setting of which I'm not aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is wanted.

In normal use disabling events would prevent the Workbook_Open() but would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property in all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer anyway. The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook) and
it
should do the trick.

Depends on the version of Office being used. The AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

I'm sure when I tested earlier the Workbook_Open() didn't fire when opened
with automation. But you are right, I stand humbly corrected!

Not only that but disabling events does not disable the Workbook_Open event.
The Workbook_Open event appears to re-enable events -

'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True

Something even more odd. After running the above and quitting Excel, next
time I manually start Excel and open my WB containing open events while
holding Shift, both open events fire - holding Shift should prevent! Then I
did exactly the same in a different version of Excel (not previously started
with automation) and Shift does prevent the open events.

Regards,
Peter T


"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is wanted.

In normal use disabling events would prevent the Workbook_Open() but
would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
The example is in the wrong application - the method works in both
applications so change Word to Excel (and Document to Workbook)
and
it
should do the trick.

Depends on the version of Office being used. The AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Hi Peter,

I can't reproduce any of that I'm afraid (Word/Excel 2003). What version of
Excel are you running?

I didn't entirely follow what you said about what you did with different
instances of Excel but, no matter what I couldn't get open events to run
when they shouldn't.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
I'm sure when I tested earlier the Workbook_Open() didn't fire when opened
with automation. But you are right, I stand humbly corrected!

Not only that but disabling events does not disable the Workbook_Open
event.
The Workbook_Open event appears to re-enable events -

'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True

Something even more odd. After running the above and quitting Excel, next
time I manually start Excel and open my WB containing open events while
holding Shift, both open events fire - holding Shift should prevent! Then
I
did exactly the same in a different version of Excel (not previously
started
with automation) and Shift does prevent the open events.

Regards,
Peter T


"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

In normal use disabling events would prevent the Workbook_Open() but
would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or
earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
The example is in the wrong application - the method works in
both
applications so change Word to Excel (and Document to Workbook)
and
it
should do the trick.

Depends on the version of Office being used. The
AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer Excel
differently than Word in this respect? Or maybe no analysis was performed at
all, in which case it was left up to the individual development groups which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had with
Word documents doesn't present itself with Excel documents and so there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I was
confused, whereas of course I replied to a post from Jezebel who I think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook and
preventing it's open events from running. I tried to clarify that when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation the
auto
open/close events do not run unless you explicitly make them. In
other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Hi Joseph,

If you read all the conversation that's been going on around your question
you will see that Peter has agreed elsewhere that they do run.

The mechanisms in Word and Excel are, however different - reflecting an
earlier time, perhaps, when the two applications were not as integrated as
they are now - I'm not a Microsoft apologist or confidante and that's the
best I can do :)

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer
Excel
differently than Word in this respect? Or maybe no analysis was performed
at
all, in which case it was left up to the individual development groups
which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had with
Word documents doesn't present itself with Excel documents and so there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I was
confused, whereas of course I replied to a post from Jezebel who I think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook
and
preventing it's open events from running. I tried to clarify that when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation
the
auto
open/close events do not run unless you explicitly make them. In
other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be
coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

you will see that Peter has agreed elsewhere that they do run.

So how do I stop them from running? the only suggestion I've seen on this
thread, is to tighten up macro security but won't this generate an
interactive prompt each time a document with a macro is opened? (Your
security settings prohibit macros from running, etc, etc, etc...) I'm trying
to streamline and optimize the performance of the document open, and
generating an interactive prompt is really going to be counterproductive for
me.

Thanks,

- Joseph Geretz -

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Joseph,

If you read all the conversation that's been going on around your question
you will see that Peter has agreed elsewhere that they do run.

The mechanisms in Word and Excel are, however different - reflecting an
earlier time, perhaps, when the two applications were not as integrated as
they are now - I'm not a Microsoft apologist or confidante and that's the
best I can do :)

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer
Excel
differently than Word in this respect? Or maybe no analysis was performed
at
all, in which case it was left up to the individual development groups
which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had with
Word documents doesn't present itself with Excel documents and so there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I was
confused, whereas of course I replied to a post from Jezebel who I
think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook
and
preventing it's open events from running. I tried to clarify that when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation
the
auto
open/close events do not run unless you explicitly make them. In
other
words
nothing to do to prevent them running automatically. Put
open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be
coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

By setting (Excel) Application.EnableEvents = False.

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
you will see that Peter has agreed elsewhere that they do run.

So how do I stop them from running? the only suggestion I've seen on this
thread, is to tighten up macro security but won't this generate an
interactive prompt each time a document with a macro is opened? (Your
security settings prohibit macros from running, etc, etc, etc...) I'm
trying
to streamline and optimize the performance of the document open, and
generating an interactive prompt is really going to be counterproductive
for
me.

Thanks,

- Joseph Geretz -

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Joseph,

If you read all the conversation that's been going on around your
question
you will see that Peter has agreed elsewhere that they do run.

The mechanisms in Word and Excel are, however different - reflecting an
earlier time, perhaps, when the two applications were not as integrated
as
they are now - I'm not a Microsoft apologist or confidante and that's
the
best I can do :)

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer
Excel
differently than Word in this respect? Or maybe no analysis was
performed
at
all, in which case it was left up to the individual development groups
which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had
with
Word documents doesn't present itself with Excel documents and so
there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I
was
confused, whereas of course I replied to a post from Jezebel who I
think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook
and
preventing it's open events from running. I tried to clarify that
when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel
startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an
AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with
automation
the
auto
open/close events do not run unless you explicitly make them. In
other
words
nothing to do to prevent them running automatically. Put
open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be
coding
directly
before opening the file?

Set m_DocExcel =
m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Thanks!

By setting (Excel) Application.EnableEvents = False.

I will give this a shot.

- Joseph Geretz -

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
By setting (Excel) Application.EnableEvents = False.

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
you will see that Peter has agreed elsewhere that they do run.

So how do I stop them from running? the only suggestion I've seen on this
thread, is to tighten up macro security but won't this generate an
interactive prompt each time a document with a macro is opened? (Your
security settings prohibit macros from running, etc, etc, etc...) I'm
trying
to streamline and optimize the performance of the document open, and
generating an interactive prompt is really going to be counterproductive
for
me.

Thanks,

- Joseph Geretz -

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Joseph,

If you read all the conversation that's been going on around your
question
you will see that Peter has agreed elsewhere that they do run.

The mechanisms in Word and Excel are, however different - reflecting an
earlier time, perhaps, when the two applications were not as integrated
as
they are now - I'm not a Microsoft apologist or confidante and that's
the
best I can do :)

--
Enjoy,
Tony

"Joseph Geretz" wrote in message
...
I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer
Excel
differently than Word in this respect? Or maybe no analysis was
performed
at
all, in which case it was left up to the individual development groups
which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened
via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had
with
Word documents doesn't present itself with Excel documents and so
there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I
was
confused, whereas of course I replied to a post from Jezebel who I
think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel
Workbook
and
preventing it's open events from running. I tried to clarify that
when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel
startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an
AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with
automation
the
auto
open/close events do not run unless you explicitly make them.
In
other
words
nothing to do to prevent them running automatically. Put
open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be
coding
directly
before opening the file?

Set m_DocExcel =
m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Hi Tony,

I must admit I'm puzzled about this. My original test was with XL2000, I've
since repeated with both Xl2000 & XL2003 in different systems but with
identical Word & Excel files and get different results. Namely in XL2k the
workbook open event fires and appears to re-enable events. XP works as
expected. Full details of my test -

Book2.xls contains -

'thisWorkbook module
Private Sub Workbook_Open()
Debug.Print "Workbook_Open " & ThisWorkbook.Name
Debug.Print Application.EnableEvents
End Sub

' Normal Module
Sub auto_open()
Debug.Print "auto_open " & ThisWorkbook.Name
End Sub

In a new session of windows, with no instances of Excel, I ran the following
from Word

' Word normal module
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\My Documents\Excel\Book2.xls"
Set oXL = CreateObject("Excel.application")

oXL.enableevents = False
Debug.Print oXL.enableevents ' xl2k - False, XP-False

Set oWB = oXL.Workbooks.Open(s)
Debug.Print oXL.enableevents ' xl2k - True, XP-False

oXL.Visible = True
'oXL.enableevents = True
'oWB.RunAutoMacros 1 'xlAutoOpen

End Sub
Sub TestClose()

'oWB.RunAutoMacros 2 'xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub


After running TestOpen I look in Excel's VBA immdeiate window and read -

xl2k -
Workbook_Open Book2.xls
True

xl-xp -
empty, ie no event fired

So despite disabling events with
oXL.enableevents = False
in xl2k the Workbook_Open has fired and reset events, indeed similar re
events is
confirmed by the two debug lines in Word, before & after opening the book.
Auto_open did not fire.

In contrast to tests before my last post, now when opening the book2.xls in
xl2k with
shift depressed disables the open events as expected.

I don't know if this difference between versions is normal or due to
something else in my xl2k system.

Regards,
Peter T
"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Peter,

I can't reproduce any of that I'm afraid (Word/Excel 2003). What version
of
Excel are you running?

I didn't entirely follow what you said about what you did with different
instances of Excel but, no matter what I couldn't get open events to run
when they shouldn't.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
I'm sure when I tested earlier the Workbook_Open() didn't fire when
opened
with automation. But you are right, I stand humbly corrected!

Not only that but disabling events does not disable the Workbook_Open
event.
The Workbook_Open event appears to re-enable events -

'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True

Something even more odd. After running the above and quitting Excel,
next
time I manually start Excel and open my WB containing open events while
holding Shift, both open events fire - holding Shift should prevent!
Then
I
did exactly the same in a different version of Excel (not previously
started
with automation) and Shift does prevent the open events.

Regards,
Peter T


"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is
opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

In normal use disabling events would prevent the Workbook_Open() but
would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
You're not wrong - it is version dependent; AutomationSecurity was
introduced in XP. If you have XP or 2003, it is the same property
in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or
earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer
anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
The example is in the wrong application - the method works in
both
applications so change Word to Excel (and Document to
Workbook)
and
it
should do the trick.

Depends on the version of Office being used. The
AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm
not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

http://support.microsoft.com/kb/211626/en-us
XL2000: EnableEvents Property Does Not Work in Automation Server

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Tony,

I must admit I'm puzzled about this. My original test was with XL2000,
I've
since repeated with both Xl2000 & XL2003 in different systems but with
identical Word & Excel files and get different results. Namely in XL2k the
workbook open event fires and appears to re-enable events. XP works as
expected. Full details of my test -

Book2.xls contains -

'thisWorkbook module
Private Sub Workbook_Open()
Debug.Print "Workbook_Open " & ThisWorkbook.Name
Debug.Print Application.EnableEvents
End Sub

' Normal Module
Sub auto_open()
Debug.Print "auto_open " & ThisWorkbook.Name
End Sub

In a new session of windows, with no instances of Excel, I ran the
following
from Word

' Word normal module
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\My Documents\Excel\Book2.xls"
Set oXL = CreateObject("Excel.application")

oXL.enableevents = False
Debug.Print oXL.enableevents ' xl2k - False, XP-False

Set oWB = oXL.Workbooks.Open(s)
Debug.Print oXL.enableevents ' xl2k - True, XP-False

oXL.Visible = True
'oXL.enableevents = True
'oWB.RunAutoMacros 1 'xlAutoOpen

End Sub
Sub TestClose()

'oWB.RunAutoMacros 2 'xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub


After running TestOpen I look in Excel's VBA immdeiate window and read -

xl2k -
Workbook_Open Book2.xls
True

xl-xp -
empty, ie no event fired

So despite disabling events with
oXL.enableevents = False
in xl2k the Workbook_Open has fired and reset events, indeed similar re
events is
confirmed by the two debug lines in Word, before & after opening the book.
Auto_open did not fire.

In contrast to tests before my last post, now when opening the book2.xls
in
xl2k with
shift depressed disables the open events as expected.

I don't know if this difference between versions is normal or due to
something else in my xl2k system.

Regards,
Peter T
"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Peter,

I can't reproduce any of that I'm afraid (Word/Excel 2003). What version
of
Excel are you running?

I didn't entirely follow what you said about what you did with different
instances of Excel but, no matter what I couldn't get open events to run
when they shouldn't.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
I'm sure when I tested earlier the Workbook_Open() didn't fire when
opened
with automation. But you are right, I stand humbly corrected!

Not only that but disabling events does not disable the Workbook_Open
event.
The Workbook_Open event appears to re-enable events -

'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True

Something even more odd. After running the above and quitting Excel,
next
time I manually start Excel and open my WB containing open events
while
holding Shift, both open events fire - holding Shift should prevent!
Then
I
did exactly the same in a different version of Excel (not previously
started
with automation) and Shift does prevent the open events.

Regards,
Peter T


"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is
opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not
aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

In normal use disabling events would prevent the Workbook_Open()
but
would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
You're not wrong - it is version dependent; AutomationSecurity
was
introduced in XP. If you have XP or 2003, it is the same
property
in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or
earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer
anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
The example is in the wrong application - the method works
in
both
applications so change Word to Excel (and Document to
Workbook)
and
it
should do the trick.

Depends on the version of Office being used. The
AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but I'm
not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Sorry Joseph, my original info was incorrect re the Workbook_Open event (in
ThisWorkbook module) though correct re Sub Auto_Open() in a normal module.

Disabling events "should" disable the workbook_open event but see my reply
to Tony, this doesn't do as expected in my XL2k as it does in XP.

Regards,
Peter T

PS
I'm confused
That was because I mistakenly read the response from Jezebel as yours.


"Joseph Geretz" wrote in message
...
I'm confused.

No, maybe I'm confused; but if so it's for good reason. Seemingly, the
development analysts at Redmond made some sort of decision to engineer
Excel
differently than Word in this respect? Or maybe no analysis was performed
at
all, in which case it was left up to the individual development groups
which
explains the statistically expected results (two tosses of the coin).

1. Word runs AutoRun macros automatically when a document is opened via
automation

2. Excel doesn't (?)

I had observed symptom #1 as described in Word and assumed that it
would be the same in Excel. But if it's not, then the problem I had with
Word documents doesn't present itself with Excel documents and so there's
nothing I need to do.

Please confirm, whether or not this is in fact the case.

Thanks!

- Joseph Geretz -

"Peter T" <peter_t@discussions wrote in message
...
Oops, thought I was replying to a response from Joseph (OP) hence I was
confused, whereas of course I replied to a post from Jezebel who I think
has
misread the OP.

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
I'm confused. You specifically asked about opening an Excel Workbook
and
preventing it's open events from running. I tried to clarify that when
using
automation they do not automatically fire. Excel does not have an
equivalent
of Word's Normal.dot.

In addition other workbooks that normally auto load on Excel startup,
such
as "checked" addins and files in the StartupPath (typically xlstart
folder),
do not open when starting an instance of Excel with automation.

Regards,
Peter T

"Jezebel" wrote in message
...
I don't know about Excel, but Word ones do. Try it: add an AutoOpen
macro
to
word's normal.dot, then, from Excel, open a Word document ...


"Peter T" <peter_t@discussions wrote in message
...
Hi Joseph,

I might be missing something in your question but with automation
the
auto
open/close events do not run unless you explicitly make them. In
other
words
nothing to do to prevent them running automatically. Put open/close
events
in a saved book and try following with / without RunAutoMacros.

Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\Temp\Book2.xls"
'Set oXL = New Excel.Application 'early binding with As
excel.Application
Set oXL = CreateObject("Excel.application")

Set oWB = oXL.Workbooks.Open(s)
oXL.Visible = True

oWB.RunAutoMacros xlAutoOpen

End Sub
Sub TestClose()

oWB.RunAutoMacros xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub

Regards,
Peter T

"Joseph Geretz" wrote in message
...
I have an application which uses Word and Excel as automation
servers.
When
opening a document, I need to prevent macros from running
automatically.

For Word documents I can do this as follows:

m_AppWord.WordBasic.DisableAutoMacros 1
Set m_DocWord = m_AppWord.Documents.Open(CStr(m_Document),
False,
True,
False)

What is the equivalent statement for Excel which I should be
coding
directly
before opening the file?

Set m_DocExcel = m_AppExcel.Workbooks.Open(CStr(m_Document))

Thanks for your help!

- Joseph Geretz

How do I prevent AutoRun Macros when Programmatically Opening Workbook?
 

Thank you Tom !!

Based on the suggested Workaround for XL2000 I did the following which
works -

Create a helper xls named Book3.xls containing

' in "Module1"
Sub SetEvents(bEnable As Boolean)
Application.EnableEvents = bEnable

End Sub

' in Word
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook
Dim oWB2 As Object

Sub TestOpen2()
Dim s1 As String, s2 As String, sPath As String
sPath = "C:\My Documents\Excel\"
s1 = "Book2.xls" ' with open events
s2 = "Book3.xls" ' with SetEvents macro

Set oXL = CreateObject("Excel.application")

If oXL.Version 9 Then
oXL.enableevents = False
Else
Set oWB = oXL.Workbooks.Open(sPath & s2)
oXL.Run s2 & "!module1.SetEvents", False
End If

Debug.Print oXL.enableevents ' False

Set oWB = oXL.Workbooks.Open(sPath & s1)
Debug.Print oXL.enableevents ' False

oXL.Run s2 & "!module1.SetEvents", True
Debug.Print oXL.enableevents ' True

oXL.Visible = True

End Sub

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/kb/211626/en-us
XL2000: EnableEvents Property Does Not Work in Automation Server

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Tony,

I must admit I'm puzzled about this. My original test was with XL2000,
I've
since repeated with both Xl2000 & XL2003 in different systems but with
identical Word & Excel files and get different results. Namely in XL2k
the
workbook open event fires and appears to re-enable events. XP works as
expected. Full details of my test -

Book2.xls contains -

'thisWorkbook module
Private Sub Workbook_Open()
Debug.Print "Workbook_Open " & ThisWorkbook.Name
Debug.Print Application.EnableEvents
End Sub

' Normal Module
Sub auto_open()
Debug.Print "auto_open " & ThisWorkbook.Name
End Sub

In a new session of windows, with no instances of Excel, I ran the
following
from Word

' Word normal module
Dim oXL As Object ' Excel.Application
Dim oWB As Object ' Excel.Workbook

Sub TestOpen()
Dim s As String
s = "C:\My Documents\Excel\Book2.xls"
Set oXL = CreateObject("Excel.application")

oXL.enableevents = False
Debug.Print oXL.enableevents ' xl2k - False, XP-False

Set oWB = oXL.Workbooks.Open(s)
Debug.Print oXL.enableevents ' xl2k - True, XP-False

oXL.Visible = True
'oXL.enableevents = True
'oWB.RunAutoMacros 1 'xlAutoOpen

End Sub
Sub TestClose()

'oWB.RunAutoMacros 2 'xlAutoClose
oWB.Close
Set oWB = Nothing

oXL.Quit
Set oXL = Nothing

End Sub


After running TestOpen I look in Excel's VBA immdeiate window and read -

xl2k -
Workbook_Open Book2.xls
True

xl-xp -
empty, ie no event fired

So despite disabling events with
oXL.enableevents = False
in xl2k the Workbook_Open has fired and reset events, indeed similar re
events is
confirmed by the two debug lines in Word, before & after opening the
book.
Auto_open did not fire.

In contrast to tests before my last post, now when opening the book2.xls
in
xl2k with
shift depressed disables the open events as expected.

I don't know if this difference between versions is normal or due to
something else in my xl2k system.

Regards,
Peter T
"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
Hi Peter,

I can't reproduce any of that I'm afraid (Word/Excel 2003). What
version
of
Excel are you running?

I didn't entirely follow what you said about what you did with
different
instances of Excel but, no matter what I couldn't get open events to
run
when they shouldn't.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
I'm sure when I tested earlier the Workbook_Open() didn't fire when
opened
with automation. But you are right, I stand humbly corrected!

Not only that but disabling events does not disable the
Workbook_Open
event.
The Workbook_Open event appears to re-enable events -

'' in Word
Set oXL = CreateObject("Excel.application")
oXL.enableevents = False
Debug.Print oXL.enableevents ' False
Set oWB = oXL.Workbooks.Open(s) ' Workbook_Open() fires !
Debug.Print oXL.enableevents ' True

Something even more odd. After running the above and quitting Excel,
next
time I manually start Excel and open my WB containing open events
while
holding Shift, both open events fire - holding Shift should prevent!
Then
I
did exactly the same in a different version of Excel (not previously
started
with automation) and Shift does prevent the open events.

Regards,
Peter T


"Tony Jollans" <My Forename at My Surname dot com wrote in message
...
In my Excel 2003, the Workbook_Open Event runs when a Workbook is
opened
via
automation - but an Auto_Open macro doesn't. Yours apparenly does
something
different so there must be some other setting of which I'm not
aware.

--
Enjoy,
Tony

"Peter T" <peter_t@discussions wrote in message
...
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

In normal use disabling events would prevent the Workbook_Open()
but
would
not prevent an auto_open() macro from running.

Not sure what all the fuss is about, as I mentioned earlier with
automation
of Excel neither of the open events are called without use of
RunAutoMacros.
A case of issue - what issue!

Regards,
Peter T

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
You're not wrong - it is version dependent; AutomationSecurity
was
introduced in XP. If you have XP or 2003, it is the same
property
in
all
apps (Word, Excel and Powerpoint anyway). If you have 2000 or
earlier,
macros are automatically trusted when documents are opened via
automation.

Looking back over the thread, it's probably the wrong answer
anyway.
The
request was for an Excel equivalent of Word's
WordBasic.DisableAutoMacros.
While not exactly the same (the applications do work
differenly),
setting
(Excel) Application.EnableEvents to False is probably what is
wanted.

--
Enjoy,
Tony

"Jonathan West" wrote in message
...

"Tony Jollans" <My Forename at My Surname dot com wrote in
message
...
The example is in the wrong application - the method works
in
both
applications so change Word to Excel (and Document to
Workbook)
and
it
should do the trick.

Depends on the version of Office being used. The
AutomationSecurity
property
certainly doesn't exist in Office 2K. It does in 2003, but
I'm
not
sure
whether it exists in Office XP.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup