Copper Contributor

Jun 02, 2026

Bulk link updates

First off, apologies if this is in the wrong community. All files are held in the same sharepoint library. So in the library there is an excel file which gets updated a couple of times a year and l...

Copper Contributor

Jun 04, 2026

Hi Virendrak, thanks for your response.

So in a flat Sharepoint there is a single Excel file, Data.xlsx and for this purpose say there are 3 word documents, Cust1.docx, Cust2.Docx & Cust3.Docx.

Data.xlsx contains 3 sheets, 1 each for Cust1 - Cust3 and the data in the sheets is in a structured format, so specific cell ranges referer to specific data catagories.

Within each docx file there are several hyperlinks which point to the catagory in the sheet, e.g, locations

On a fairly regaular basis the Data.xlsx file is regenerated by an application and moved into the sharepoint share with a new name. The old Data.xlsx is archived and the new file Data1.xlsx becomes the latest.

At this point all the hyperlink pointers in Cust1, 2 & 3 need renewing to pint to Data1,

Now, there is an option to rename Data to DataArchive or something and name the new file as Data if that would work. We also have the option to copy the new xlsx file into the library and when Sharepoint prompts to replace the old file, however I've not been able to get this to work as I've been unable to access the original xlxs file once its been replaced. I can see it in version history but thats it.

I've been playing with versions of the following and whilst it updates the hyperlinks and the hyperlink then opens the new file, It throws an error on the reference so wont change to the correct sheet / cell range

Sub ForceSharePointDeepLinkSuccess()

Dim hl As Hyperlink

Dim baseFile As String, sheetName As String, cellRef As String

Dim finalUrl As String

' 1. Paste your clean SharePoint path (No ?web=1 or ?d= suffixes)

' Source: Excel > File > Info > Copy Path

baseFile = "https://sharepoint.com"

' 2. Specific destination.

' Use single quotes around the sheet name if it contains spaces.

sheetName = "'Sheet1'"

cellRef = "A1"

' 3. Combined URL using the # separator

finalUrl = baseFile & "#" & sheetName & "!" & cellRef

For Each hl In ActiveDocument.Hyperlinks

' Overwrite the address with the FULL deep link

hl.Address = finalUrl

' CRITICAL: Clear the SubAddress property entirely

' Leaving this property with data triggers the "Reference isn't valid" error

hl.SubAddress = ""

Next hl

MsgBox "Hyperlinks updated to: " & finalUrl

End Sub

Obviously with the correct path etc

cheers

virendrak

Steel Contributor

Jun 04, 2026

Make sure you do not change the original file name (Data.xlsx).

Whenever a new file is available, make sure to create an archive copy.

First, take the existing Data.xlsx file and create a copy (Data_Copy_ForArchive.xlsx) of it.
Move that copy to the archive location and rename it using a consistent naming pattern, such as:
- DataArchive1.xlsx, DataArchive2.xlsx, DataArchive3.xlsx, and so on. You can apply a consistent renaming logic for archived files not for the original file.
Once the archive copy is safely created, you can proceed to overwrite the original file with the new one using the same name: Data.xlsx.
- This will overwrite the existing file in SharePoint (and create a new version if versioning is enabled).

By keeping the file name, the same (Data.xlsx), all existing links in your Word documents will continue to work without breaking.

Forum Discussion

Bulk link updates