Group Policy for Excel Data Connection/Macros MOSS 2007

Posted on 2010-11-09
Medium Priority
Last Modified: 2012-05-10
We have an excel xlsm sheet that uses a data connection from our SharePoint 2007 server and has macros.

I'm getting tired of explaining to people that they have to firse allow the macros and then allow the data connection and the links when they open the spreadsheet.

We have a windows server 2003 environment, Active Directory, MOSS 2007.

Is there a way to set a group policy on the server so that everybody's excel program knows it can trust data connections and macros from our SharePoint site?

Thank you
Question by:itkadmin
  • 2
  • 2
LVL 11

Accepted Solution

jkpieterse earned 2000 total points
ID: 34102599
You could buy a code signing certificate, sign the macros (then it is just a matter of checking the "Trust all macros from this publisher" checkbox to enable macros in future).

After that, I'd turn off automatic updating of queries and have VBA handle the update.

Author Comment

ID: 34102690
Good idea, but unfortuneately I can't imagine finance authorizing a couple hundred $/yr so people don't have to click a button (this is for a sheet on our network, behind our firewall, so public integrity isn't an issue) . Any other ideas?
LVL 11

Assisted Solution

jkpieterse earned 2000 total points
ID: 34102764
You can create your own certificate using selfcert.exe (in your Office setup folder), but it would require each user to install that certificate for it to work.

Author Closing Comment

ID: 34135757
I used your second solution, but I can certainly see where the first would be better.

Thank you!

Featured Post

Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

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.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

600 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