A script to update href links in excel xlsx files when you move files

Update-ExcelHyperlinksAddress.ps1

[cmdletbinding()]
Param(
[switch]$CopyToNew,
$SearchString = "\\OLDSERVER\OLDSHARE\FOLDERNAME",
$ReplaceString = "\\NEWSERVER\NEWSHARE\NEWFOLDER",
$FolderToSearch = "\\server\share\folder\",
$LogResultsLocation = $PSScriptRoot
)

$Excel = New-Object -ComObject "Excel.Application"
$Excel.Visible = $false
(ls $FolderToSearch -Recurse -File -Filter *.xlsx) | % {
	$LinksUpdated = 0
	$Workbook = $Excel.Workbooks.Open($_.FullName)
	$Workbook.Sheets | %{
		$_.HyperLinks | ? {$_.Address -and $_.Address -match [regex]::Escape($SearchString)} | %{
			$LinksUpdated++
			$_.Address = $_.Address.Replace($SearchString,$ReplaceString)
		}
	}
	
	
	if ($CopyToNew -and $LinksUpdated)
	{
		$Workbook.SaveAs($_.FullName + " - Copy.xlsx")
	}
		
	if ($LinksUpdated) {
		$Workbook.Save()
		$Workbook.Close()
	}
	else
	{
		$Workbook.Close($false)
	}
	
	"'{0}': Updated $LinksUpdated links" -f $_.Name | Out-File $LogResultsLocation\Log.log -Append
}
$Excel.Quit()
$Excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()