Open a workbook from VBA and disable Workbook_Open() code?

I am opening spreadsheets using VBA and a couple of the workbooks contain code which starts executing when Workbook_Open() is called.

How can I open the workbooks using VBA but stop the code automatically executing? I am only opening the workbooks to look at formulae in the sheet- I do not want any code execution.

Solution 1:

Would you like to try disabling the Events before you open the workbook in VBA and then re-enabling them for the rest of the module? Try using something like this:

Application.EnableEvents = False   'disable Events
workbooks.Open "WORKBOOKPATH"      'open workbook in question
Application.EnableEvents = True    'enable Events

Solution 2:

I don't know why this was not clearly mentioned in the other answers but I found Application.AutomationSecurity to do exactly what was required. Basically

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

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

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

Even after the code is run the settings will not revert back to the default behavior so you need to change it again. Thus for this question

previousSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
' Your code
Application.AutomationSecurity = previousSecurity

Solution 3:

Here another way to open with out the vba

Start Excel Application > Go to File > Recent >

Hold Shift key and double click to open -

Doing this will prevent the Workbook_Open event from firing and the Auto_Open macro from running.

Or hold shift key and double click to open the Workbook.

For VBA Work with Application.EnableEvents property (Excel)

Solution 4:

A combination of Application.EnableEvents and a Workbook specific Application.EnableEvents works great. Any time the workbook is re-referenced (such as copying cells) it will retrigger the activate events. The workbook has to exit first, and cant be accessed after closing so try this:

Dim wb as Workbook
Application.EnableEvents = False
Set wb = workbooks.Open "YOURWORKBOOKPATH"
Application.EnableEvents = True
wb.Application.EnableEvents = False

**Code**

wb.Application.EnableEvents = True
wb.Close