Office Trusted locations

I'm curious how to best approach this situation. I have an old VBA workbook that works just fine as is. Unfortunately with the new security measures in Office 2007/2010 you get the "Security Warning Some active content has been disabled" message. I know that I can click on the message and select enable content or add it to a trusted location. Unfortunately doing this every single time is a bit of a pain for end-users. So I had created a Setup project in Visual Studio that would launch a console application that would copy the file to the templates folder then place a short-cut on the desktop to it. Maintaining it is a bother though because I don't add updates to 开发者_运维技巧the Excel file, an engineer does. So I have to re-create a setup.exe for 32/64 bit.

What is the best solution?

It needs to work with Windows Vista/7 32/64 bit and Office 2007/2010 32 bit and the users will vary in computer skills.

Send this link to all the users. It's a pretty good walk-through of how to make sure that your file opens with macros enabled. It also gives you a few different ways of doing it depending on your situation.

http://office.microsoft.com/en-us/excel-help/enable-or-disable-macros-in-office-files-HA010354316.aspx

I have a similar situation and took care of it with some registry entries.

HKCU\Software\Microsoft\Office\12.0\Excel\Security\Trusted Locations\AllowNetworkLocations=1 [DWORD]
HKCU\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM=1 [DWORD]
KHCU\Software\Microsoft\Office\12.0\Excel\Security\VBAWarnings=1 [DWORD]
KHLM\Software\Microsoft\Office\Common\Security\UFIControls=1 [DWORD]

Maybe a quick google search on VBA security registry entries can help you.

Late to the game here, but this is a common annoyance: you need to define a 'Trusted Location'.

Most developers encounter the problem you're seeing when their code tries to open a spreadsheet file, and they get this unhelpful error message:

"Office has detected a problem with this file. To help protect your computer this file cannot be opened."

If you're intermediate-to-expert as a VBA coder (or with any common scripting language) look up the Trusted Location code published by Daniel Pineault on DevHut.net in 2010:

DevHut code example: Trusted Location using VBScript

For your convenience, here is my implementation of it in Excel:

Public Sub TrustThisFolder(Optional FolderPath As String, _
                           Optional TrustSubfolders As Boolean = True, _
                           Optional TrustNetworkFolders As Boolean = False, _
                           Optional sDescription As String)


' Add a folder to the 'Trusted Locations' list so that your project's VBA can
' open Excel files without raising errors like "Office has detected a problem
' with this file. To help protect your computer this file cannot be opened."

' Ths function has been implemented to fail silently on error: if you suspect
' that users don't have permission to assign 'Trusted Location' status in all
' locations, reformulate this as a function returning True or False

' Nigel Heffernan January 2015
'
' Based on code published by Daniel Pineault in DevHut.net on June 23, 2010:
' www.devhut.net\2010\06\23\vbscript-createset-trusted-location-using-vbscript\

' **** **** **** ****  THIS CODE IS IN THE PUBLIC DOMAIN  **** **** **** ****

' UNIT TESTING:
'
' 1:    Reinstate the commented-out line 'Debug.Print sSubKey & vbTab & sPath
' 2:    Open the Immediate Window and run this command:
'           TrustThisFolder "Z:\", True, True, "The user's home directory"
' 3:    If  "Z:\"  is already in the list, choose another folder
' 4:    Repeat step 2 or 3: the folder should be listed in the debug output
' 5:    If it isn't listed, disable the error-handler and record any errors
'

On Error GoTo ErrSub

Dim sKeyPath    As String

Dim oRegistry   As Object
Dim sSubKey     As String
Dim oSubKeys    ' type not specified. After it's populated, it can be iterated
Dim oSubKey     ' type not specified.

Dim bSubFolders         As Boolean
Dim bNetworkLocation    As Boolean

Dim iTrustNetwork       As Long

Dim sPath   As String
Dim sDate   As String
Dim sDesc   As String
Dim i       As Long

Const HKEY_CURRENT_USER = &H80000001

bSubFolders = True
bNetworkLocation = False

If FolderPath = "" Then
    FolderPath = FSO.GetSpecialFolder(2).Path
    If sDescription = "" Then
        sDescription = "The user's local temp folder"
    End If
End If

If Right(FolderPath, 1) <> "\" Then
    FolderPath = FolderPath & "\"
End If

 

sKeyPath = ""
sKeyPath = sKeyPath & "SOFTWARE\Microsoft\Office\"
sKeyPath = sKeyPath & Application.Version
sKeyPath = sKeyPath & "\Excel\Security\Trusted Locations\"
 
 
 
Set oRegistry = GetObject("winmgmts:\.\root\default:StdRegProv")
'   Note: not the usual \root\cimv2  for WMI scripting: the StdRegProv isn't in that folder
 
oRegistry.EnumKey HKEY_CURRENT_USER, sKeyPath, oSubKeys

For Each oSubKey In oSubKeys

    sSubKey = CStr(oSubKey)
    oRegistry.GetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", sPath
    
    'Debug.Print sSubKey & vbTab & sPath
        
    If sPath = FolderPath Then
        Exit For
    End If
    

    
Next oSubKey

If sPath <> FolderPath Then

    If IsNumeric(Replace(sSubKey, "Location", "")) Then
        i = CLng(Replace(sSubKey, "Location", "")) + 1
    Else
        i = UBound(oSubKeys) + 1
    End If
    
    sSubKey = "Location" & CStr(i)
    
    If TrustNetworkFolders Then
        iTrustNetwork = 1
        oRegistry.GetDWORDValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", iTrustNetwork
        If iTrustNetwork = 0 Then
            oRegistry.SetDWORDValue HKEY_CURRENT_USER, sKeyPath, "AllowNetworkLocations", 1
        End If
    End If
    
    oRegistry.CreateKey HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey
    oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Path", FolderPath
    oRegistry.SetStringValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "Description", sDescription
    oRegistry.SetDWORDValue HKEY_CURRENT_USER, sKeyPath & "\" & sSubKey, "AllowSubFolders", 1
    
End If

ExitSub:

    Set oRegistry = Nothing
    Exit Sub

ErrSub:
    
    Resume ExitSub

End Sub

Do, please, keep the acknowledgements in the code if you reuse it: this will distinguish you (and StackOverflow) from other posts and other sites where experts (and others) exchange knowledge without acknowledgement.