Tuesday, February 5, 2008

VBScript to change Excel Macro Security

The script below will change the Excel Macro Security to Low. I created it for the purpose of changing the macro security level to Low for running reports. The server was also used to edit Excel reports and thus the security always got changed to medium or high while people edited reports and often forgot to change it back to Low. Hence, the popup asking to enable/disable macros causing the report to sit there waiting for user input. This script ran prior to the scheduled reports running to ensure the security level was where it should be.

5 comments:

J Akers said...

awesome bit of code, thanks!

Vipin said...

Could you please also tell thet where we need to past this code so that it can work properly.
I tried pasting this in a module. but doesn't work.

frozen267 said...

I saved the code as a .vbs file and then run it. Following which, I opened excel to check whether the macro security level was set to low and it wasnt. Did I miss out on something? Mind you, I am logged into the local admin user.
Please help~

Dave said...

Sorry I haven't been updating/moderating my blog for a while. I see the two comments about it not working. Vipin this meant to be a standalone .vbs script file not part of the actual Excel code...so this won't work in a module.

Frozen267, What error where you getting? Did you check the registry keys mentioned to see if they are updating when you manually change it? What version of Excel are you using? I believe I created this for 2003 and seemed to work for previous versions but didn't test on 2007 so it might be a different setting. If it is I will look into it and update please post a reply to this one. Thanks.

Dave said...

In response to myself and Frozen267, if you aren't running Excel 2003 this will not work unless you change the version string from 11.0 to whatever the designation for the version you have installed of Excel.