How do I prevent AutoRun Macros when Programmatically Opening Workbook?

[Joseph Geretz]

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

 

[Gary Keramidas]

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

 

[Joseph Geretz]

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

 

[Jezebel]

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

 

[Harlan Grove]

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

 

[Peter T]

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

 

[Jezebel]

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]

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

 

[Jonathan West]

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

 

[Peter T]

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

 

[Tony Jollans]

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.

 

[Tony Jollans]

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

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

 

[Peter T]

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

 

[Jonathan West]

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.

 

[Tony Jollans]

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.

 

[Peter T]

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]

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.

 

[Peter T]

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]

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.

 

[Joseph Geretz]

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 -