3

I have a macro in Excel. Part of that macro opens up other workbooks using Workbooks.Open(Filepath).

Some of the workbooks I'm opening have (badly done) VBA code inside of them, that try to then calculate a UDF and fail horribly.

Without resorting to an On Error Resume Next or an On Error GoTo, how do I say "Open this file, DO NOT RUN ANY CODE".

Trying to avoid letting the code execute in the first place due to possible security concerns. I feel comfortable enough opening these files, and there's a decent chance that they're not going to be compromised, but breaches can happen, and why have a hole when I don't need one? I also don't want error messages interrupting my code executing.

Searching the web just shows me shift+open opens the file without executing code, which is not at all what I'm looking to do. I'm looking for the equivalent VBA method.

Improve this question
3
  • 1
    If you hold the left shift key down while opening the workbook it won't run the Workbook Open code: jkp-ads.com/Articles/preventopenevent.asp Commented Aug 17, 2018 at 4:26
  • @JerryJeremiah: Sorry if I wasn't clear, I'm trying to programatically open files. Commented Aug 17, 2018 at 4:49
  • 1
    @ScottHoltzman: The code provided below is neatly killing UDF's in the files I'm opening Commented Aug 17, 2018 at 21:19

2 Answers 2

Reset to default
5

AutomationSecurity is likely what you want:

https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.AutomationSecurity

MsoAutomationSecurity can be one of these MsoAutomationSecurity constants.

msoAutomationSecurityByUI . Uses the security setting specified in the Security dialog box.

msoAutomationSecurityForceDisable . Disables all macros in all files opened programmatically without showing any security alerts. Note This setting does not disable Microsoft Excel 4.0 macros. If a file that contains Microsoft Excel 4.0 macros is opened programmatically, the user will be prompted to decide whether or not to open the file.

msoAutomationSecurityLow . Enables all macros. This is the default value when the application is started.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Anyone stumbling on this, please also read Ahmed's answer. The two answers were submitted literally the same minute, and combined together make an excellent picture of what's going on, and how to solve it
4

You can disable the macros for newly opened files, open the workbook, and then re-enable the macros:

Private Sub OpenWorkBookMacroDisabled(wbPath As String)
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Workbooks.Open (wbPath)
    Application.AutomationSecurity = msoAutomationSecurityByUI
End Sub
Improve this answer

3 Comments

This is an excellent and informative answer. The documentation, linked above, mentions using msoAutomationSecurityLow as the default to return to. Why did you select ByUI as the default to return to?
@Selkie It's up to you whether you want to set it to Low or ByUI following the guidelines in the documentation page provided by Tim in his answer. msoAutomationSecurityByUI means that if you tried to open another XLSM file after that without setting it to ForceDisable (by mistake, possibly), it will (probably, based on settings) display a warning message asking whether to enable or disable macros (similar to what happens when opening the file manually).
msoAutomationSecurityLow, on the other hand, will enable the macros and possibly run code without any notifications (unless you use ForceDisable, of course). msoAutomationSecurityLow is the default though, as you suggested. I just wanted to show you the other option. I was going to include a link to the docs after posting the answer so you know what each option means, but Tim beat me to it :D Therefore, there was no need to post that again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.