Recently a client was going through the process of migrating all of their documents from their network into SharePoint. They had some existing excel models that contained links/references to other excel files on the network. Problem was, if/when these models are migrated into SharePoint, the links break. This article explains the process I came up with to address the issue.
If you want to test out my steps below, you can download the following two excel files and use them as samples:
- Container (or Destination) File: This file contains the link(s)
- Linked (or Source) File: This file is linked to from the Container (or Destination) File
Steps to migrate linked excel files
You will need to manually re-link the files after moving them to SharePoint. The good thing is, SharePoint will prompt you to update the links each time you open the document. And an error will be thrown if the linked file is not accessible.
Here’s the working process:
[Assumption: Both source files (files that are linked) and destination files (files that contain the links) will be migrated to SharePoint]
Step 1: Map the document library where the files were migrated to as a network drive. Let’s call it drive Z.
Step 2: Manually open all excel files one by one (if the destination files are easily identified, then only open those). Open the files in edit mode. A prompt similar to this will appear each time (click to enlarge):
Step 3: Press Update
Step 4: From my testing, if the original folder hierarchy and file names in Windows file system matches the folder hierarchy and file names used in SharePoint (after the migration), the update succeeds without additional prompts. The update also succeeds if the file has been opened and linked before. However, if this is the first time the file is opened and if SharePoint cannot find the source file to link to, the following error will be thrown (click to enlarge):
Step 5: Press OK. This window appears:
Step 6: Press Edit Links. You will get a window that allows you to update the source links. Select a broken source (highlighted in yellow below):
Step 7: Press Change Source. A browse window appears that lets you browse to the mapped network drive we created earlier (drive Z).
Browse to the correct file inside SharePoint and select it:
Step 8: Save the excel spreadsheet. Going forward, each time you open this file, you will only get the first prompt to update. No errors will be thrown.
Testing
After migrating your linked documents and completing the above steps, you can confirm that the links still work as expected by updating the source document – the destination document should be automatically updated as well.
So far, I haven’t found an automatic way of doing this. If you find a way to automate this, please share in your comments.
Hi Ehi – Great article thanks.
with re to
https://ehikioya.com/migrating-linked-excel-files-to-sharepoint/
Could you please elaborate Step 1. thanks.
Step 1: Map the document library where the files were migrate
d to as a network drive. Let’s call it drive Z
Hi Chet,
SharePoint allows you to map a document library as a network drive – thus allowing you to access the document library in a similar way that you would access any other drive on your PC (like the C:\ drive for example).
To map a library as a network drive, you start from Windows explorer where your drives are listed. On the “Computer” tab, press “Map network drive” and enter the document library url.
Hope that helps.
Hi Ehi,
our tool ReplaceMagic (http://www.replacemagic.com) allows people to change links in Office documents which are part of SharePoint.
It is interesting that we see more and more SharePoint migrations where RM is used. My estimate that out of sold licenses like 30% are used after SP migration.
If you think that this might be interesting for you I can provide more details.
BR,
Oliver
ReplaceMagic team
Hi Ehi,
Is this base on a SharePiont server migration or a SharePoint online tenant migration.
I have a customer with 2 drives that need to be migrated to SharePoint online tenant with tone of files with links and I cant find anything.
I look forward to your reply
Hi Phil,
This was based on SharePoint on-premise (SharePoint server). However, I believe linked excel files should work the same way in SharePoint online. You can do a quick test in SharePoint online using the two sample linked excel files in this article. In the end, it’s all about file system hierarchy. If you are able to maintain the same relative hierarchy that the files had on the network in SharePoint, the links should continue to work.
Please let us know your results after you have tested this.
Thanks Ehi,
I will try it today and let you know.
Kind regards
Hi Ehi,
I have another question regarding Office 365 tenants.
I have a customer A that owns customer B, both with their own Office 365 environments. Customer A wants to merge Customer B into a single tenant under Customer A. They want to merge Mail, SharePoint, Archive and OneDrive.
My questions is, have you ever done this? do you know of a way to do it? are there any tools/software that can be used to complete it quick and easy?
Kind regards
Phil
Hi Phil,
I have not had to do this yet. But I think the most challenging piece will be the mail boxes. You might find this resource useful in this regard.
Hope that helps.
Hello,
We have multiple clients facing this problem, and in some instances it may be worth following these steps to manually update links once a user opens the files.
In large scale deployments we’ve started to look at https://www.linktek.com/
Although not used it yet, their support and management team are very helpful.
Just another option for some people.
Cheers,