Set Excel Trusted Locations Login Script
 

I have a problem with an Excel workbook that I am trying to handle 2
different ways. For the purpose of this post I'm interested in knowing how I
can create a trusted location using a vb script, batch script, or registry
edit.

I recently created a workbook that most people in my company need to use.
Everyone's Office 2003 installation has Macro security set to "High". Rather
than walk around to all of the workstations adjusting macro security and/or
setting a trusted location I'd like to just add the folder that houses this
workbook as a Trusted Location but I can't figure it out.

I don't have Sharepoint (I did find a script for this in the MSDN for
Sharepoint) and we don't use Group Policy.

Thanks very much for the help,

MJ

Answer: Set Excel Trusted Locations Login Script
 

Hi MJ,

I can definitely help you with creating a trusted location in Excel using a VB script or registry edit. Here are the steps:

1. VB Script Method:

1. Open Notepad or any text editor and paste the following code:
   
   Formula:

   ---

   Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Path\To\Your\Workbook.xlsx")
objExcel.Application.AutomationSecurity = 1
Set objTrustedLocation = objExcel.FileValidation.Add(1, "C:\Path\To\Your\Folder", 1)
objExcel.Workbooks.Close
objExcel.Quit 


   ---

2. Replace "C:\Path\To\Your\Workbook.xlsx" with the path to your Excel workbook and "C:\Path\To\Your\Folder" with the path to the folder that contains your workbook.
3. Save the file with a .vbs extension (e.g. "trustedlocation.vbs").
4. Double-click the .vbs file to run the script. This will open your workbook, set the macro security to "Enable all macros", and add the folder as a trusted location.

2. Registry Edit Method:

1. Open the Registry Editor by pressing Windows key + R, typing "regedit", and pressing Enter.
2. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Security\Trusted Locations (Note: "11.0" is for Office 2003. If you have a different version of Office, the number may be different.)
3. Right-click on Trusted Locations and select New Key.
4. Name the new key "Location1" (or any other name you prefer).
5. Double-click on the (Default) value in the right pane and enter the path to the folder that contains your workbook.
6. Double-click on the AllowSubFolders value and change the value data to 1.
7. Close the Registry Editor.

That's it! Your folder should now be a trusted location in Excel and your workbook should be able to run macros without any security warnings.

Let me know if you have any questions or if there's anything else I can help you with.

Best regards,
[Your Name]

Set Excel Trusted Locations Login Script
 

You want some code which overrides users' security settings?

Virus writers would love to have that code.

Excel 2003 does not have "Trusted Locations"

It has "Trusted Publishers" and to become Trusted you must provide a digital
signature.

You can purchase one of these from Verisign or Thawte or similar providers.

See Excel 2003 on digital certificates.


Gord Dibben MS Excel MVP

On Mon, 28 Dec 2009 10:27:01 -0800, powlaz
wrote:

I have a problem with an Excel workbook that I am trying to handle 2
different ways. For the purpose of this post I'm interested in knowing how I
can create a trusted location using a vb script, batch script, or registry
edit.

I recently created a workbook that most people in my company need to use.
Everyone's Office 2003 installation has Macro security set to "High". Rather
than walk around to all of the workstations adjusting macro security and/or
setting a trusted location I'd like to just add the folder that houses this
workbook as a Trusted Location but I can't figure it out.

I don't have Sharepoint (I did find a script for this in the MSDN for
Sharepoint) and we don't use Group Policy.

Thanks very much for the help,

MJ

I agree with the Gord here, you can have a verified Digital Signature which you can use for the security of your document, and there are so many verisign provider on the internet, you can just find the website of the service providers and can buy a digital certificate and signature for you.

____________
Digital Signature