CODEWITHSUNDEEP
excelvba
ella
ella

Reputation: 195

How to open an excel without running the Workbooks.Open macro in it

I have a vba code that calls my procedure whenever openning that excel.

Sub WorkBook_Open()
Call Sheets("Result").main
End Sub

My concern is , that main function will send email out. So in future if I want to edit that excel, how can i open it without triggering the call?

My initial intention was to use command line to run the vba function everyday and send that report automatically

Upvotes: 9

Views: 34213

Answers (5)

Ron Matuszek
Ron Matuszek

Reputation: 48

I've also had issues when trying to prevent the "Workbook_Open" macro from running when opening an Excel file by holding down the shift key. It used to work every time but like so many other things with Windows 11 and Office 365, Microsoft isn't too concerned with making things work anymore.

The most reliable way I've found to prevent the auto macro from firing is to put the Excel VBA engine into break mode before opening the file. Just place a breakpoint in any macro, {F9} key, and then run this macro until VBA stops. While in break mode the VBA engine won't respond to any other macro event so you can open the file you want to edit without the "Workbook_Open" macro running.

Upvotes: 1

rumle001
rumle001

Reputation: 11

Since some certain version, holding shift no longer works. I added the following to reinstate the functioning:

Private Sub Workbook_Open()

Application.OnKey Key:="{+}", Procedure:="StopMacro" '+ is the shift key https://learn.microsoft.com/en-us/office/vba/api/excel.application.onkey

Call MakeRec

End Sub

Sub StopMacro()

' do nothing.

End Sub

Upvotes: 1

Auction God
Auction God

Reputation: 109

You do not need to open Excel first. Simply hold down the shift key while double-clicking the .xlsm file.

Upvotes: 0

WhyGeeEx
WhyGeeEx

Reputation: 499

I've found the following works for me in Office 365 (maybe other versions of Excel too) -

  1. Open Excel directly, do not double-click a workbook to start Excel
  2. File/Open/Browse
  3. Navigate to the workbook you want to open, single click on it
  4. Click the dropdown next to Open, and Open in Protected View

If you want to edit further at that point, you'll have to click Enable Editing on the popup that should appear just under your menu bar. When I do that, I have macros that try to run right away, but they fail, and I can edit the VBA. YMMV there.

enter image description here

Upvotes: 3

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

  1. Open the Excel Application.
  2. Go to File ~ Open, or File ~ Recent.
  3. Hold Shift while opening your file.

NB: This won't work by just selecting the Excel workbook to open.
You need to open Excel first and then open the workbook.

Edit:

My concern is , that main function will send email out.

Don't add your code to the Workbook_Open event, add it to the click event of a button instead - it'll never send out emails until you press the button then.

Upvotes: 12

Related Questions