Excel 2007 security

[Steve_in_Kent]

The majority of our users are using excel 2003.

i have several spreadsheets in use, where the vba code does some important functionality. however, our IT department has rolled out excel 2007 on a few machines across the site. I myself have 2007.

however, vba doesn't run automatically on this version. how do i get around this?
my searching just yields something about certificates that i dont understand.

how can i ensure this code runs automatically?

Steve

[HansV]

The easiest way is to instruct the Excel 2007 users to either move the workbooks to one of their trusted locations, or to add the folder(s) containing the workbooks to their trusted locations. Macros in workbooks stored in a trusted locations will always be allowed.

Trusted locations can be viewed and set by clicking the Office button > Excel Options > Trust Center > Trust Center Settings > Trusted Locations.

See Create, remove, or change a trusted location for your files for more info.

[Steve_in_Kent]

Yea, i've done that for myself. but,, we are talking a lot of users. and their.. shall we say ''PC knowledge'' is extremely low in some cases.

is there a way for a user to click a batch file, or vba, or something that would MAKE a trusted area, automatically from their pc. ?
then copy the file there, and put the shortcut on their desktop..

Steve

[HansV]

Setting trusted locations for all users is way beyond me, but you'll find a description in Configure trusted locations and trusted publishers settings in the 2007 Office system.

You can copy a file and create a desktop shortcut using VBScript; here is some script for the latter:

Code: Select all

Dim WSHShell
Dim strDesktopPath
Dim strFilePath
' Substitute path and filename of workbook
strFilePath = "..."
Set WSHShell = CreateObject("WScript.Shell")
' Read desktop path using WshSpecialFolders object
strDesktopPath = WSHShell.SpecialFolders("Desktop")
If Not Right(strDesktopPath, 1) = "\" Then
  strDesktopPath = strDesktopPath & "\"
End If
With WSHShell.CreateShortcut(strDesktopPath & "\MyWorkbook.lnk")
  ' Set shortcut object properties and save it
  .TargetPath = strFilePath
  .Save
End With
Set WSHShell = Nothing

[stuck]

however, our IT department has rolled out excel 2007 on a few machines across the site

How about asking your IT people if it is possible for them to tweak whatever script they are using to deploy Office 2007 to set a common trusted location for all PCs? You could add too that the more they roll out Office 2007 the more this issue is likely to come back and bite them so it might save them grief later if they can fix it now.

Ken

[HansV]

That's a good idea!

[Leif]

A couple of things to look at / check out:

How to: Trust Workbook Locations Using Script
and
Script for adding Trusted Locations in MS Office 2007 : Microsoft Office 2007, vbscript

[stuck]

I think I'll start keeping a tally, that's two thumbs-up I've had from Hans in as many days. Perhaps I should start a thread in Lounge Matters suggesting this as a way of rewarding people?

Ken

[Steve_in_Kent]

thanks for the suggestions guys.. i need to find a solution, that for sure.!

Steve

[Steve_in_Kent]

Hmm would i be correct in thinking that the script version, will only work if you have admin access to the machine in question?

im trying to add a server location. however, at the moment, its coming up with a syntax error, and i know i dont have the .exe file it asks for on my machine.

steve

[Steve_in_Kent]

Well.. my internet searches have guided me to a program called caspol.exe. which sits on .net machines.

apparently this can alter security settings. but, am i barking up the wrong tree here. i have a GUI version of caspol.exe. however, i cannot get it to work.

and im not sure it does what i think its suppoed to do ?

[rory]

To the best of my knowledge caspol only relates to .Net assemblies. You need a group policy for the macro security (there are administratvie templates available from MS) but your IT department really ought to be doing this as you almost certainly won't have the right permissions.