05-12-2019 03:47 PM
05-12-2019 03:47 PM
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?
05-12-2019 10:20 PM - edited 05-12-2019 10:21 PM
@Suzanne Hunt Take a look at the VBA in this page
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.
05-13-2019 05:20 PM
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 :-)
05-13-2019 11:12 PM
@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
'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them
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)
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
'In Case of Cancel
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
'Change First Worksheet's Background Fill Blue
wb.Worksheets(1).Range("A1:Z1").Interior.Color = RGB(51, 98, 174)
'Save and Close Workbook
'Ensure Workbook has closed before moving on to next line of code
'Get next file name
myFile = Dir
'Message Box when tasks are completed
MsgBox "Task Complete!"
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
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.
by Ingeborg Hawighorst on May 13, 2020
by Brian Jones (OFFICE) on May 06, 2020
by cuong on April 15, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020