Linked Excel Files and SharePoint

Linked Excel Files and SharePoint

V: 1.0

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
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
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.

Error:
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
MS Excel Container File Warning – Linked file not Reachable

You may get following warning message.

Error:
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
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
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 – 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!

Error:
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
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.

Error:
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
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.

Download the Test Excel Container File and Linked File

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
MS Excel Container File Opened in Office Web Apps in SharePoint – Linked file not supported

Few Resources on the Excel Linked files:

2 Responses to “Linked Excel Files and SharePoint”

  1. Robert says:

    thanks for this article. but I do not know what would be the solution if you have both linked files in sharepoint and you just open them in local excel. what path should i specify on edit links dialogue ?

    • admin says:

      @Robert
      Use the UNC path of the Sharepoint source Excel file in your destination excel file to link the two files.

      We’ll create a blog post on this topic.

Leave a Reply

Enter the CAPTCHA * Time limit is exhausted. Please reload CAPTCHA.