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.
 

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,084,753
Messages
5,379,657
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top