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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top