Linked Excel Files and SharePoint
I’ve been working lately with Microsoft Excel data report files that were linked and were uploaded to SharePoint. There are few scenarios where the linked files can cause issues if not used properly. I’m describing few scenarios below:
Scenario 1: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded both files to SharePoint
Scenario 2: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded only container file to SharePoint
Scenario 3: User created MS Excel files on the desktop, linked those files (desktop or shared drive). then uploaded both files to SharePoint and updated the linked files using their SharePoint links
Scenario 2 is common among Excel users. Users create linked excel files and upload only the main file aka the container file. In most cases the linked file was on the desktop of the user. Now when another user tries to open the Excel container file from SharePoint or from their desktop by copying it, they get errors / warning messages. That is due to container file not able to reach to author’s desktop file from SharePoint.
I copied the container excel file on my desktop and went through it.
|MS Excel Container File that has linked files|
Click on the File menu > Info. Under security warning section you will see the warning details.
|MS Excel Container File that has linked files – File Info Security Warning section|
Now you click on Enable Content from either the top middle of file or within File > Info section in an attempt to get the complete data and you are presented with a warning as shown in following image.
We can’t update some of the links in your workbook right now.
You can continue without updating their values, or edit links you think are wrong.
|MS Excel Container File Warning – Linked file not Reachable|
You may get following warning message.
This workbook contains links to one or more external sources that could be unsafe.
If you trust the links, update them to get the latest data. Otherwise, you can keep working with the data you have.
|MS Excel Container File Warning – Linked file could be unsafe warning|
How to view the Linked Files in Container Excel File:
To view the linked files from container Excel file, go to Data ribbon tab and under Connections group click Edit Links.
|MS Excel Container File – Edit Links to Linked File|
In Edit Links window, if you select a source, you will see its location. In situations, you will find it pointing to the local desktop profile of users. Nice work with the data report, but not nicely executed.
|MS Excel Container File – Edit Links window|
MS Excel Container File on SharePoint and Linked file not reachable:
Now if you try to open the file directly from SharePoint, you will get warning messages. See the URL address has been appended with the linked file address!
We can’t connect to http://sharepoint.domain.com/users/WindowsProfile/desktop/excelfile.xls
|Open MS Excel File from SharePoint – Linked file missing – See URL|
Click Ok and you will be greeted with another warning. See the image below.
Microsoft Excel cannot access the file http://sharepoint.domain.com/users/WindowsProfile/desktop/excelfile.xls. There are several possible reasons.
- The file name or path does not exist
- The file name is being used by another program
- The workbook you are trying to save has the same name as a currently open workbook.
|Open MS Excel File from SharePoint – Linked file missing – Possible Reasons listed|
My Treat for Linked Excel Files and SharePoint:
I’ll cover the other details in of different testing scenarios may be little later as time permits. I created a container and linked files with some test data. Feel free to download and play around with it on your SharePoint test farm.
Note it down that office Web apps doesn’t support linked files. You will get prompts when viewing the Excel container file using Office Web Apps in SharePoint.
|MS Excel Container File Opened in Office Web Apps in SharePoint – Linked file not supported|
Few Resources on the Excel Linked files:
- Break a link to an external reference
- How to find and list all links (external references) in Excel?