Updating Hyperlinks in multiple files

[Suzanne Hunt]

Hi Everyone,

I'm working with a customer that wants to copy a document library to another site collection (both in SharePoint Online). They have a number of excel spreadhseets that have hyperlinks that need to be updated to the new site collection (e.g. from abc.sharepoint.com/documentcenter/documents/filename to abc.sharepoint.com/sites/doccenter/documents/filename)

Is there a way to scan all the documents and update them rather than do it manually?

Many Thanks,

Suzanne

[#2]

@Suzanne Hunt  Take a look at the VBA in this page 

https://excel.tips.net/T003358_Changing_Portions_of_Many_Hyperlinks.html

It can be easily adjusted to loop through all worksheets in the active workbook. The code can live in another workbook, of course. You'd open the workbook that has the code, then open the workbook with the hyperlinks, then start the macro.

If you need help adjusting the VBA, pipe up.

[#3]

Hey @Ingeborg Hawighorst, thanks for this. Was hoping to avoid having to open each one individually, have just run a quick Flow over the document library getting documents with "Workpapers" in the title (they're the ones that need updating) and it looks like there are 438 of them...but I cant see an easy way of using Flow so it might just have to be done like this...will have a play with the VBA :)

[#4]

@Suzanne Hunt Conceptually, it is possible to use VBA to

- get a list of all files in a directory

- loop over all these files and open only the ones that match certain criteria

-- loop over all worksheets in the open file

--- do the loop over all cells that swaps out the URL

For the first bit, the code is

Sub LoopAllExcelFilesInFolder()
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
'SOURCE: www.TheSpreadsheetGuru.com

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Change First Worksheet's Background Fill Blue
      wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
    
    'Save and Close Workbook
      wb.Close SaveChanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      myFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 found here.

Where the code changes the fill colour to blue, insert the code that loops through the worksheets and replaces the URL part.

Let me know how you get on.