Excel & SharePoint embedded Hyperlinks

Question

Hello community!

I am hoping somebody can assist me with a rather niche situation. Here is the scoop! The goal is to build a repository (multiple documents consisting of PDF's, workbooks, word docs within sub-folders), link them all to 1 master Excel Workbook. We then want to download this whole repository to a flash drive and send it to an auditor.

Here is the issue: we have built the master workbook, and the various files connecting to it are all uploaded to SharePoint. We have made some links (testing) that are hyperlinks to SharePoint version of PDF (example). When we download this whole repository to a flash drive, all the links break or continue to try opening the SharePoint versions. Is it possible to build the repository in SharePoint, drag it down to a local drive, and the links will look at the local copies only?

I hope I am explaining this well enough... Thank you for any assistance, in advance.

Answer 1

Dear Novalesk,

Generally, you can just create the master Excel file in a site library in SharePoint Online>open it in Excel Online>Hyperlink>copy the URLs of the files in it to create the hyperlinks.

Then share the PDF files, the documents and the workbooks with the auditor.  When the auditor open the downloaded master file, he can click on the hyperlinks to access the files.

If something is misunderstood, welcome to give some examples about your requirements for our further clarification.

Best Regards,

Cliff

Answer 2

Hello and thank you for your reply.

That part indeed works correctly and has been tested to confirm is working, while the workbook and "resource" documents are in SharePoint. 

The issue (maybe working as intended?) is if we take the repository off of SharePoint, move it to a local drive or USB flash drive. When I do that, the links placed in the workbook (while it was still in SharePoint) are opening the documents that are still in SharePoint. We are attempting to target redundancy, that is the goal. 

The hopeful goal is to build the repository in SharePoint, copy it to a USB flash drive and we are wanting the links to hopefully redirect towards the flash drive copies without having to remake the the links 1-by-1. Currently after moving the repository to a local drive, the established links are still opening the SharePoint copies rather than the local copies. 

I hope this clears things up a little bit, and thank you for any advice or direction.

Answer 3

Dear Novalesk,

Thanks for your confirmation and I understand that you want the file paths to be converted to the local file paths automatically when you download the master workbook.

Generally, you may use VBA code to bulk-update the links to the local file paths in the Excel files. So the requirement may be achieved by VBA code.

However, the online URL's file path has an unique file source id in SharePoint Online and the VBA code may not locate the URL ids for changing the file paths. So I think it may need advanced VBA code. As our forum has an Excel Programming category for VBA related issues and questions, I'll move the thread to that category to see if the community members can give some code achieving your requirements.

Thanks for your understanding.

Cliff