Open an Excel workbook without executing Excel macros


In Excel VBA, it happens quite a lot that we would open up a workbook to apply a number of steps to its contents. Usually these workbooks are free of VBA code themselves. However, recently, I encountered the situation where the source workbook also contains macros. This in itself does not force us to use different coding. But it becomes a different story when the source workbook contains code itself that is run op opening of the workbook (Workbook_Open event or similar event coding). This code would fire and it would not necessarily be what the developer wants.

As it turns out, VBA contains a method to disable macros in such a source workbook:

'This is the default behavior where each time it would ask whether you want to enable or disable macros:
Application.AutomationSecurity = msoAutomationSecurityByUI

'This option would disable all macros in newly opened files:
Application.AutomationSecurity = msoAutomationSecurityForceDisable

'This option would enable all macros in newly opened files:
Application.AutomationSecurity = msoAutomationSecurityLow

Therefore, you would temporarily turn off the security and reset it to the level of security it was before:

Dim previousSecurity As Long

previousSecurity = Application.AutomationSecurity
If previousSecurity <> msoAutomationSecurityForceDisable Then
   Application.AutomationSecurity = msoAutomationSecurityForceDisable
End If
' Your code, including opening workbooks containing Workbook_Open events
If Application.AutomationSecurity <> previousSecurity Then
   Application.AutomationSecurity = previousSecurity
End If

That's it !


Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links