Open an Excel workbook without executing Excel macros
- Apr. 4, 2021
Introduction
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 !
