Forum Discussion
Updating Hyperlinks in multiple files
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
3 Replies
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.
Hey IngeborgHawighorst, 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 :-)
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 Subfound 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.
