Excel blocks external data connections by default to protect you from security risks. When you open Diligent files, you may see warnings that prevent your data from loading properly. Setting up a Trusted Location tells Excel these files are safe—eliminating warnings and ensuring your data connections work every time. This is a one-time setup that takes about 2 minutes.
Why This Matters
Diligent Excel files connect to external data sources (Analysis Services cubes) to deliver live, refreshable reports. By default, Excel blocks these connections and displays security warnings because it doesn't know if the source is safe. Without a Trusted Location configured, you'll encounter repeated interruptions and potential data refresh failures.
Common Errors Without Trusted Locations
If you skip this setup, you may encounter errors like these when opening Diligent files:
- "Either the user does not have access to the database, or the database does not exist" — Often appears with a RootActivityId and timestamp
- "An operation that uses the database driver could not be completed" — Suggests reinstalling Microsoft Query, but the real fix is trusting the file location
- "External Data Connections have been disabled" — Yellow security warning bar at the top of your workbook
- "Security Risk: Microsoft has blocked macros from running because the source of this file is untrusted" — Red banner with no Enable Content option
- Pivot tables showing #REF! errors or failing to refresh data
What Triggers These Warnings?
Excel uses a security feature called the Mark of the Web (MOTW) to identify files downloaded from the internet or external sources. Files with this mark are treated as potentially unsafe, and their data connections are blocked until you tell Excel they're trusted.
| Warning Type | What It Means |
|---|---|
| Yellow Security Warning Bar | "External Data Connections have been disabled" — Click Enable Content to allow the connection temporarily |
| Red Security Risk Banner | "Microsoft has blocked macros from running because the source of this file is untrusted" — File needs to be unblocked or placed in a Trusted Location |
| Data Connection Blocked | Pivot tables fail to refresh, show errors, or display stale data from the last successful connection |
Setting Up a Trusted Location
Creating a Trusted Location is a one-time setup. Once complete, any Diligent files saved in that folder will open without security warnings and connect to data automatically.
Open Excel Options
In Excel, go to File → Options. This opens the Excel Options dialog where you'll configure security settings.
Navigate to Trust Center
Select Trust Center from the left-hand menu, then click the Trust Center Settings... button.
Open Trusted Locations
In the Trust Center window, select Trusted Locations from the left menu. Click Add new location... at the bottom.
Configure the Folder
Click Browse and select the folder where you store your Diligent Excel files (for example, Documents\Diligent Files).
✅ Check these important options:
- Subfolders of this location are also trusted — Includes all subfolders
- Allow Trusted Locations on my network — Required for SharePoint, OneDrive, or network drives
Save and Test
Click OK to save the location, then OK again to close Trust Center, and OK to exit Excel Options. Move your Diligent files to this folder and reopen them — no more warnings!
Network and Cloud Locations
If your Diligent files are stored on a network drive, SharePoint, or OneDrive, additional configuration is required.
Network Locations Require Extra Step
By default, Excel doesn't allow network paths as Trusted Locations. Before adding your network folder, check the box labeled "Allow Trusted Locations on my network (not recommended)" at the bottom of the Trusted Locations settings.
OneDrive and SharePoint Considerations
OneDrive and SharePoint files can be tricky because Excel sometimes sees them as coming from two different paths: the web URL and the local synced folder. Both paths may need to be trusted.
| Location Type | Example Path | Notes |
|---|---|---|
| Local OneDrive | C:\Users\[Name]\OneDrive\Diligent Files |
Synced folder on your PC — add this as a Trusted Location |
| SharePoint URL | https://company.sharepoint.com/... |
May also need to be added; enable network locations first |
| Network Drive |
\\server\share\Diligent or G:\Diligent
|
Use UNC path (\\server\...) for most reliable results |
In Trust Center, go to Trusted Documents and check "Allow documents on a network to be trusted". This helps with OneDrive files that Excel treats as network locations.
Alternative: Unblock Individual Files
If you only need to trust a single file (rather than a whole folder), you can remove the "Mark of the Web" from that specific file. This is useful for one-off situations.
Additional Trust Center Settings
Beyond Trusted Locations, other Trust Center settings can affect how Diligent files behave. Here's what else you should review:
External Content Settings
Navigate to File → Options → Trust Center → Trust Center Settings → External Content.
| Setting | Recommended | Why |
|---|---|---|
| Data Connections | Prompt user about Data Connections | Balances security with usability; click Enable Content when prompted |
| Workbook Links | Prompt user on automatic update | Allows linked workbooks to update after confirmation |
Not Recommended: "Enable All Data Connections"
While selecting "Enable all Data Connections" will eliminate warnings, it's a security risk because it allows connections from any file, not just trusted ones. Use Trusted Locations instead for a secure solution.
Troubleshooting
Warnings Still Appear After Setup
File location: Confirm the file is actually saved inside the Trusted Location folder, not just opened from there. Copy or save the file to the trusted folder.
Subfolders enabled: If the file is in a subfolder, make sure "Subfolders of this location are also trusted" is checked.
Network locations: For network drives, SharePoint, or OneDrive, verify "Allow Trusted Locations on my network" is checked.
Restart Excel: Close all Excel windows completely and reopen the file. Trust Center changes sometimes require a restart.
File still blocked: Try unblocking the file directly via Properties → Unblock checkbox, even if it's in a Trusted Location.
Settings Are Greyed Out
If Trust Center settings are greyed out and you can't change them, your organization's IT department has likely enforced security policies via Group Policy. Contact your IT administrator to request the appropriate settings be enabled for Diligent files.
Unblock Checkbox Not Visible
The Unblock checkbox only appears when a file has the "Mark of the Web" attribute. If you don't see it:
- The file may have been created locally (not downloaded)
- Your organization may have a policy that strips the mark automatically
- Use PowerShell instead:
Unblock-File -Path "C:\path\to\file.xlsx"
Network Path Rejected
If Excel says "The path you have entered cannot be used as a Trusted Location for security reasons":
- First, check "Allow Trusted Locations on my network" at the bottom of Trusted Locations
- Try using the UNC path format:
\\servername\share\folderinstead of a mapped drive letter - For some network drives, you may also need to add the server to Windows Trusted Sites (Control Panel → Internet Options → Security → Trusted Sites)
Quick Reference
Best for: Folders with multiple Diligent files you use regularly
Best for: Single files you've downloaded once
Best for: One-time access when prompted
Related Articles
Setting up a Trusted Location is essential for a smooth experience with Diligent Excel files. This one-time configuration tells Excel that your Diligent files are safe, eliminating security warnings and ensuring your data connections work reliably. If you continue to experience issues after following these steps, or if settings are locked by your organization, contact your IT department or EBM Support for assistance.
Comments
0 comments
Article is closed for comments.