?
Solved

Open Excel 2007, Enable macros programatically

Posted on 2008-11-02
4
Medium Priority
?
5,544 Views
Last Modified: 2012-05-05
I need to open up an exsiting Excel 2007 file.  When I open the file, how do I programatically set the security options for enabling the macro and data connection
0
Comment
Question by:MichelleLacy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Expert Comment

by:CSLARSEN
ID: 22863492
I would think that it would not be possible.
Because IF it would be possible it would almost eliminate the value of the macro protection part.
However sometime experts come up with brilliant ideas............so I will follow this thread
cheers
cslarsen
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22863537
After consideration I think it is ok to answewr this question but first I will flag it with the mods to be sure

Cheers

Dave
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 22868470
MichelleLacy,

Without revealing how to set the Excel macro security setting, there are two things to know about Excel:

First, you can instruct Excel to enable macros by installing a digital certificate in the workbook (see below).

Second, when you open an Excel instance using automation (as I am assuming you are doing since you appear to be coding in C#) and then open a workbook using automation, Excel enables macros by default without consideration of the macro security settings.

---

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
 

Author Closing Comment

by:MichelleLacy
ID: 31512473
This is something I will look into, for now I point my app to copy the file into a company trusted site that automatically allows macros and data content.  I dide not know this directory existed until I asked somone on the IT Network Security team.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question