How to open an .xlsm file with Macros ENABLED, always?

By default, all documents in Excel 2007 open with a security warning. By the 100th time in the year, you would have thought Microsoft/Office/Excel has marked the file as safe.
LVL 58
cyberkiwiAsked:
Who is Participating?
 
byundtCommented:
Excel 2007 lets you specify a folder as "allow all macros to be enabled". You do this in the Developer...Macro Security...Trusted Locations menu item. If you make it your development folder, then each new workbook you create there automatically inherits the "macros automatically enabled" feature.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Files are never marked safe. Although 2010 seems to be doing something like this but I haven't explored it enough to be sure.

The only way to mark a file as safe is to add a digital certificate. An unofficial digital certificate can be added to an Excel workbook so that, after a one time interaction with the user where the user either allows or disallows that digital certificate, the user can then repeatedly open the workbook and run macros without any warnings at all, even if their security setting is high. Note that a network administrator can lock the list of trusted sources and prevent a user from adding the certificate to the list rendering this technique ineffective. Also note that the user will not be able to run signed macros if the security setting is set to Very High (the Very High setting was introduced in Excel 2003.)

An extended description of how to create and use digital certificates can be found at http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=194. A brief tutorial is included below.

To create a free unsecured code-signing digital certificate locate and run SelfCert.exe. It is an optional utility installed as part of the Office installation found in the Office installation directory. If not found, open the Add/Remove Programs control panel, run the Office Installer, and install Digital Signature for VBA projects under Office Tools. When run SelfCert asks for a name - enter any meaningful name or description. The text entered is displayed to the user whenever they are asked to accept or decline the digital certificate. For more information see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q217221.

Once the digital certificate has been created on the development system, open the workbook and press ALT+F11 to open the VBE. Select the menu command Tools->Digital Signature. Click Choose and select the desired digital certificate (they are listed by name.) Click OK. Click OK again. The workbook now contains a digital signature. Since the development system already has the digital signature installed, the workbook will now open on that system without any macro warning prompt.

To allow the workbook to run without the macro security warning on another system the digital certificate has to be installed on those systems as well. Copy the workbook to a target system and open it. Excel will present the Security Warning dialog. Click Details to show the Digital Signature Details dialog. Click View Certificate to show the Certificate dialog. Click Install Certificate. Click Next twice and then Finish. Click Yes when the prompt is displayed asking if the certificate should be installed. Click OK on the completion dialog. Click OK twice more to close the widows. Click Enable Macros on the Security Warning to open the workbook. The next time the workbook is opened the same security dialog will be displayed and the check box "Always trust macros from this publisher" will be enabled - check it on and click "Enable Macros". In the future any workbook with that digital certificate installed will open without a macro security warning.

A certificate can also be installed by sending just the certificate as a separate file. To create the certificate file, open the "Internet Options" control panel, navigate to the Content tab, click Certificates, select the certificate, click Export, click Next three times, click Browse and navigate to a known folder, enter a certificate file name, click Save, click Next, click Finish, click OK, click Close to close the Certificates dialog, and click OK to close the "Internet Options" control panel. The certificate file can be sent to anyone and, when opened, will display the Certificate dialog. Clicking "Install Certificate" leads the user through the same installation process described above. Once installed, all workbooks with that digital signature will open without any warnings.

Since it is relatively easy for someone to forge an unsecured digital certificate and place it in a malicious workbook, this method of avoiding macro security warnings may not be an acceptable solution. In the event that a decision is made to abandon this technique the installed digital certificates can be easily removed by opening the Internet Options dialog from either Internet Explorer or from the control panels and navigating to the Content tab. Click on Certificates. Find the certificates to be deleted (they should be in either the Trusted Root Certification Authorities or Other People section,) selecting them, and click the Remove command button. Click Done when finished.

If true security is required then a secure digital certificate can be purchased from any of the many Certificate Authorities. See Microsoft's list of Certificate Authorities at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/rootcertprog.asp. Certificates are purchased for periods of time and average $500 per year.

For more information on macro security settings in Office, see http://office.microsoft.com/en-us/assistance/HA011362661033.aspx.

Kevin
0
 
cyberkiwiAuthor Commented:
Thank you for your solution.  I am not sure if you are the right person to help me, as you seem to refer to 4 different links for help.

Ima gonna to wait and see if there are other experts who can come up with a non-certificate way to solve the problem.

I am upping the points to 21 because it seems this could be a difficult challenge.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
zorvek (Kevin Jones)ConsultantCommented:
Eat my shorts.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Oh, I know! Remove the macros!
0
 
rspahitzCommented:
Darn...I should have held out for 25 pts!
0
 
dlmilleCommented:
Is it too late to jump in?

Try this link:  http:#a35269307

Or, a really good E-E discussion had a solution for this - go here, and scroll down to the bottom - there's a link there you can use: http:/Q_26923514.html


Cheers,


Dave
0
 
cyberkiwiAuthor Commented:
I'm glad I held out for more help. dlmille your links gave the most bang for comment - the 2nd one was really helpful.  I have increased the points to 23 so that I can equitably distribute enough points to each helper when the time comes.

I will hold out a bit more to see if anyone has a better solution.  I have to say that Rob has the inside running on this.

@zorvek http:#a35269602 - I'm sorry but my macros track my points at EE in real time and draws a nice graph after pulling data from EE.  I needz them.

[fyi - This is a real question. I followed Rob's article and have done it in all of <2 minutes... pretty good]
0
 
rspahitzCommented:
yay!  My outdated article got its ~2 minutes of fame...oh, wait...that's supposed to be 15 minutes...hmmm...
0
 
cyberkiwiAuthor Commented:
See, I knew there was a GREAT answer waiting to happen.  You've saved the day again, my good sir.
0
 
cyberkiwiAuthor Commented:
Office button
Excel Options button (bottom right in menu next to Exit)
Trust Center (2nd from bottom on the left)
Trust Center Settings button (somewhere on the right)
Trusted Locations (2nd from top on the left)
Add new location!!!

Thanks to Brad
0
 
cyberkiwiAuthor Commented:
Brilliant - exactly what I was looking for. No need to tinker with certificates.

Thanks Rob also for the article, and Kevin for the attempt.  You both get as many points as offered when you entered the question.
0
 
dlmilleCommented:
Yea - when I got Excel 2007, I made every drive I had a Trusted Location, lol.

Where's my 3 points - I sent a link to the right answer?

Were's the "request attention" button?

:)

Cheers,

Dave
0
 
rspahitzCommented:
Cool answer BY.  Now I just need to add every directory on my hard drive where I may start a new workbook :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.