Forum Discussion

PhreeRun's avatar
PhreeRun
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 lets say 20 word fiiles containing roughly 5 links per file referencing 4 ranges in the excel file. These word documents are also updated several times a year.

When either the excel documented if updated a new file if generated with a new name which replaces the old one and the old one is archived.

The word documents are simply edited and renamed with the old one being archived.

I'm looking for a way to update the links in the word documents to pick up the same ranges in the new spreadsheet. As it currently stands I need to manually update each link which is time consuming, prone to error and stupid :-)

anyone come across this need and if so how did you solve it?

 

thanks in advance

Document Library
files
SharePoint Online
Usage

3 Replies

  • PhreeRun's avatar
    PhreeRun
    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's avatar
      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.

      1. First, take the existing Data.xlsx file and create a copy (Data_Copy_ForArchive.xlsx) of it.

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

  • virendrak's avatar
    virendrak
    Steel Contributor
    Jun 03, 2026

    Can you please explain your scenario with specific examples and file names? That will help me give you a more accurate answer.

    If you are replacing the same Excel file and keeping the same name or new name , and overwriting it with the new version / or name, then the link issue in Word can be fixed/automated easily.

    But if you are creating a completely new Excel file every time, and moving the old one to an archive, then the situation becomes more complicated.

    For example: Let’s say the Excel document currently has ID = 1. You create a new Excel file, and now the new file has ID = 2, and the old file (ID = 1) is moved to the archive. In this case, Word cannot automatically update the links, because the latest document always gets a new ID.

    When the file ID changes every time, automation becomes difficult, and a manual approach is often the only reliable option since the “latest” file always has a different ID and name combination.