Opening an Excel file and NOT run Workbook_Open macro

ScanGuard

New Member
Joined
Mar 4, 2014
Messages
4
Hi there,

I have an excel enabled file that needs to open another Excel file and copy an image from that file, however, this Excel file that I wish to open has a "Private Sub Workbook_Open()" macro that runs and has a couple of messages that require a response.

As I wish to have the opening of the file performed by vba, how do I suppress the workbook open macro?

My code at present is

Workbooks.Open Filename:=strFile

where strFile is the path and file name of the file I wish to open.

Thanks in advance.
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

K e v i n

Board Regular
Joined
Mar 1, 2014
Messages
109
Hi,
There are two methods for auto running macros:
1. In Workbook_Open as you are doing
2. Create a routine called Auto_Open

When a workbook is opened normally both run but when opened using VBA only Workbook_Open should run. So if you move the code from Workbook_Open to Auto_Open this should fix your problem.

When loading a Word document using VBA you can use
WordBasic.DisableAutoMacros 1 'Disables auto macros
WordBasic.DisableAutoMacros 0 'Enables auto macros
but I don't know of an Excel equivalent - perhaps someone else does
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Use Application.EnableEvents = False to disable events triggering,
Don’t forget to restore it to True at the end of the code
See the below example:
Rich (BB code):
Sub Test()
 
  ' Full pathname to the source file, change to suit
  Const strFile As String = "C:\Temp\test.xls"
 
  ' Trap errors for restoring of EnableEvents as well
  On Error GoTo exit_
 
  ' Switch off events triggering to disable Workbook_Open actions
  Application.EnableEvents = False
 
  ' Main
  With Workbooks.Open(Filename:=strFile, ReadOnly:=True)
    ' Your code is here, below is just for testing
    .Sheets(1).Copy
    .Close False
  End With
 
' If error happens then code jumps to this label
exit_:
 
  ' Restore eveny=ts triggering
  Application.EnableEvents = True
 
  ' Show details of code error
  If Err Then MsgBox Err.Description, vbCritical, "Error #" & Err.Number
 
End Sub
 
Last edited:

Forum statistics

Threads
1,089,666
Messages
5,409,624
Members
403,272
Latest member
Karl Matacz

This Week's Hot Topics

Top