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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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:

Watch MrExcel Video

Forum statistics

Threads
1,090,249
Messages
5,413,301
Members
403,474
Latest member
Rampestamper

This Week's Hot Topics

Top