Forum Discussion
Migrate Excel files with a lot of links to SharePoint Online?
This is quite problematic for our users too, a lot of users got caught out by creating spreadsheets in OneDrive sync folder on their machine, linking there and then sharing the files. The Excel has a link reference to C:\user\<name>\One Drive - <Tenant Name>\Test.xlsx etc But of course the person using the file does not have access that persons c: drive so the <name> bit doesn't exist on their local drive.
Links to https:\\<onedrive location> tend to also be a little slow.
So its tricky, plus links like this can easily break spreadsheets and cause errors. I'm not sure I can count the times that someone has come to me asking me to check their spreadsheet because they think it might be wrong but they can't see where the issue is. Normally its a link or copying down etc.
If it was me, I would use the new feature built into Excel called PowerQuery, on the data tab, using SharePoint Folder link and avoid using links all together ... but this is a lot of work to change, but the long term benefits are huge.
This effectively queries the other spreadsheet, using a simple interface, so no need for vloopup or linking or calculations in the spreadsheet ... all calculations can be done in the query, so they are repeatable and difficult to break. Plus as a bonus, if you copy the query which it builds it can be pasted into PowerBI and you can create dashboards very quickly that are available anywhere.
Hi Julian
one tool that might help is ReplaceMagic (check: https://www.replacemagic.com/). ReplaceMagic supports changes in all Office document formats so this might help you here also.
In case of any questions you can always contact us.
Btw. we work natively with SharePoint (you can connect to it directly from ReplaceMagic). More infos: https://www.replacemagic.com/HowToUseSharePoint.aspx
BR,
Oliver
(I work for ReplaceMagic)
Did anyone find a good way to handle this?
Finally, which tools you choose for your migraion? LinkFixer Advanced, ReplaceMagic or others? Thanks.
- Linkfixer - worked like a champ.
- So there are 2 strategies to apply:
• SharePoint Online
o Migration needs to be done using the SharePoint Migration Tool (SharePoint Migration Tool for SharePoint and OneDrive - Migrate to SharePoint and OneDrive | Microsoft Docs). Can use an Azure Databox (Migrate your file share content to SharePoint using the Azure Data Box - Migrate to SharePoint and OneDrive | Microsoft Docs), but should only use if your data is in a remote location. Keyless AFD in <city name> will help with data transfer.
o Pro’s
Cost included in the O365 licenses depending on the plan you have. SharePoint Online Plan 2 is certainly the best option.
Document versioning and access control.
Easier sharing and collaboration.
Enterprise search.
Keyless Azure Front Door (AFD) here in <city name> for SharePoint Online & OneDrive for Business.
Can be synced to device using the OneDrive for Business sync tool,
o Con’s
No UNC pathname.
End user change management.
• Azure Files
o Pro’s
Easy migration with lift & shift of the file system.
Replicate your UNC pathnames providing limited end user impact.
Depending on the amount of data, you could transfer the data using Azure Databox.
o Con’s
More expensive. Cost’s to consider would be for a sync server, snapshot and of course the storage cost.
If deterministic path, you would need to use Azure VPN gateway or ExpressRoute. Additional cost. Microsoft uses our product internally (for the past 3 years in multiple areas internally), as well as NASA (3 research centers), Intel, HP (deployed globally), Ford (used by 12K site collection admins). We have over 40K users using our product, the SharePoint Essentials Toolkit.
Our product fixes links within MS Excel formulas, PDF, MS Word, web pages, web parts etc. and can be found from our website at https://www.qipoint.com
It also can handle digitally signed documents correctly so not to modify them and invalidate signatures, MS Office Online will keep link integrity when opened with MS Word/Excel online, etc..
Thank you Chris....I've spoken to your team but it's incredibly expensive compared to others.
The guy's trying to get a simple solution to a problem and it become Battle of the Vendors.
Have you read this?
https://support.office.com/en-us/article/open-or-change-source-workbooks-of-external-references-links-ae3e687c-97ae-4d9b-b684-8756a5301253
What about doing a find/replace of the link across multiple workbooks? Requires macro and the code can be found on multiple sites/forums... here's 1:https://excel.tips.net/T005598_Searching_Through_Many_Workbooks.html
Hi,
as document migration is controlled process (I hope ;)) you always know what is/was your source location and what will be new location. In case that you use ReplaceMagic you have two options:
- you fix your files before you move them to new location. Here you'll basically specify what is source location (like \\serverABC) and what will be new location (like, https://company.sharepoint.com) and let ReplaceMagic do the job
- or you copy everything to SP.Online and then do the same thing again
Of course, there are variations like you copy documents from source location to some temporary storage, fix them (again search for source, replace with new location) and upload them to SP.Online
Key here is that you know what you copying and where you are copying (source-target relation).
ReplaceMagic is used by tons of customers and majority of migrations are now going direction SharePoint.Online (occasionally to SharePoint on-prem) so with RM can achieve your targets.
Plus price tag is very, very competitive to other tools :). You can check prices over https://www.replacemagic.com/store.aspx
reditguy wrote:Did anyone find a good way to handle this?
Hi reditguy,
We ended up buying a product called LinkFixer Advanced by Linktek.
https://www.linktek.com/
// Julian
