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,081,902
Messages
5,361,964
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top