I'm looking for a PowerShell solution that updates all url links in all excel files in bulk. The excel files are stored in a SharePoint document library. I've transitioned to a newer version of SharePoint and I have a ton of excel files on SharePoint which have the old SharePoint URL links in them that need to be updated to the newer one. The difference between the old SharePoint URL links and the new SharePoint URL links is the domain. Here's a script that I created (which doesn't work):
#Variables
$DomainURL = "[domain url link]"
$SiteURL = "[sharepoint site url]"
$LibraryName = "[SharePoint Library Name]"
$OldLink = "[old sharepoint site]"
$NewLink = "[new sharepoint site]"
Try
{
#Connect to SharePoint Online
Connect-PnPOnline -Url $SiteURL -UseWebLogin
$Web = Get-PnPWeb
#Create Excel.Application COM object.
$excel = New-Object -ComObject excel.application
#$excel.visible = $True
#Get all Excel documents from the library
$Documents = Get-PnPListItem -List $LibraryName -PageSize 500 | Where {$_.FieldValues.FileRef -like "*.xls*"}
ForEach($Document in $Documents)
{
Try
{
$DocumentPath = $DomainURL + $Document.FieldValues.FileRef
Write-host -f Yellow "Processing Document:"$DocumentPath
#Open the Document
$workbook = $excel.workbooks.Open($DocumentPath)
#Get all links from the document
#Looping the Sheets to check all the sheets in the Excel
for ($loop_index = 1; $loop_index -le $workbook.Sheets.Count; $loop_index++)
{
#Assigning the hyperlinks to the Variable from each sheet
$Sheet = $workbook.Worksheets($loop_index).Hyperlinks
#looping the Sheet one by one and store it in collection
ForEach($Link in $Sheet)
{
#Get all links from the sheet
If ($_.Address.ToLower().Contains($OldLink.ToLower()) -or $_.TextToDisplay.ToLower().Contains($OldLink.ToLower()))
{
#Get the current Link and Link Text
$CurrentLink = $_.Address
$CurrentText = $_.TextToDisplay
#Update Link and Description
$_.Address = $_.Address -Replace $OldLink, $NewLink
$_.TextToDisplay = $_.TextToDisplay -Replace $OldLink, $NewLink
$workbook.save()
$Document.Update()
Write-host -f Green "`tLink '$CurrentText' at '$CurrentLink' has been updated!"
}
}
}
}
Catch {
write-host -f Red "Document Update Error:" $_.Exception.Message
}
Finally {
$ExcelDocument.Close()
}
}
}
Catch {
write-host -f Red "Error:" $_.Exception.Message
}
Finally {
$Word.quit()
}
When trying to run the script above, I get the following errors:
Document Update Error: You cannot call a method on a null-valued expression.
Error: You cannot call a method on a null-valued expression
Please Help!!
