求助：PowerShell批量更新SharePoint Excel文件URL链接失败

问题背景 #

迁移至新版SharePoint后，需批量替换大量Excel文件中的旧SharePoint域名URL为新域名，原编写的PowerShell脚本运行报错：

Document Update Error: You cannot call a method on a null-valued expression.
Error: You cannot call a method on a null-valued expression

原脚本错误分析 #

• 循环变量误用：ForEach($Link in $Sheet)循环中错误使用管道变量$_，应改为循环变量$Link
• 未定义变量调用：Finally块中调用未定义的$ExcelDocument.Close()，实际应为$workbook.Close()
• 错误的COM对象关闭：末尾错误调用$Word.quit()，需改为关闭Excel COM对象$excel.Quit()，同时要清理COM对象避免内存泄漏
• 在线文件操作风险：直接通过在线URL修改Excel易出现权限或同步问题，应先下载到本地修改再上传
• 冗余操作：$Document.Update()无需调用，文件修改后上传覆盖原文件即可

修复后的完整脚本 #

# 配置参数
$SiteURL = "[SharePoint站点URL]"
$LibraryName = "[文档库名称]"
$OldLink = "[旧SharePoint域名/URL前缀]"
$NewLink = "[新SharePoint域名/URL前缀]"
$TempFolder = "$env:TEMP\SPExcelLinkUpdate"

# 创建临时目录
if(-not (Test-Path -Path $TempFolder)){
    New-Item -Path $TempFolder -ItemType Directory | Out-Null
}

Try {
    # 连接到SharePoint Online
    Connect-PnPOnline -Url $SiteURL -UseWebLogin

    # 获取文档库中所有Excel文件
    $Documents = Get-PnPListItem -List $LibraryName -PageSize 500 | Where-Object {
        $_.FieldValues.FileRef -like "*.xls*" -and $_.FieldValues.FileSystemObjectType -eq 0
    }

    # 初始化Excel COM对象
    $excel = New-Object -ComObject Excel.Application
    $excel.Visible = $false
    $excel.DisplayAlerts = $false

    foreach($Document in $Documents){
        Try {
            $fileName = $Document.FieldValues.FileLeafRef
            $fileRelativePath = $Document.FieldValues.FileRef
            $localFilePath = Join-Path -Path $TempFolder -ChildPath $fileName

            Write-Host -ForegroundColor Yellow "处理文件: $fileName"

            # 下载文件到本地临时目录
            Get-PnPFile -Url $fileRelativePath -Path $TempFolder -FileName $fileName -AsFile -Force

            # 打开本地Excel文件
            $workbook = $excel.Workbooks.Open($localFilePath)
            $linkUpdated = $false

            # 遍历所有工作表
            foreach($sheet in $workbook.Worksheets){
                # 遍历工作表中的所有超链接
                foreach($link in $sheet.Hyperlinks){
                    $addressUpdated = $false
                    $textUpdated = $false

                    # 检查并替换链接地址
                    if($link.Address -and $link.Address.ToLower().Contains($OldLink.ToLower())){
                        $oldAddress = $link.Address
                        $link.Address = $link.Address -replace [regex]::Escape($OldLink), $NewLink
                        $addressUpdated = $true
                    }

                    # 检查并替换链接显示文本
                    if($link.TextToDisplay -and $link.TextToDisplay.ToLower().Contains($OldLink.ToLower())){
                        $oldText = $link.TextToDisplay
                        $link.TextToDisplay = $link.TextToDisplay -replace [regex]::Escape($OldLink), $NewLink
                        $textUpdated = $true
                    }

                    # 如果有更新则保存并输出日志
                    if($addressUpdated -or $textUpdated){
                        $workbook.Save()
                        $linkUpdated = $true
                        Write-Host -ForegroundColor Green "`t已更新链接: 原地址='$oldAddress' | 原文本='$oldText'"
                    }
                }
            }

            # 如果文件有更新，上传回SharePoint覆盖原文件
            if($linkUpdated){
                $workbook.Close()
                Set-PnPFile -Path $localFilePath -Folder $fileRelativePath.Replace("/$fileName","") -Checkout -Overwrite
                Write-Host -ForegroundColor Cyan "`t已将更新后的文件上传回SharePoint"
            } else {
                $workbook.Close()
                Write-Host -ForegroundColor Gray "`t文件中未找到需要更新的链接"
            }

            # 删除本地临时文件
            Remove-Item -Path $localFilePath -Force
        }
        Catch {
            Write-Host -ForegroundColor Red "文件处理错误: $($_.Exception.Message)"
            if($workbook){ $workbook.Close($false) }
            if(Test-Path $localFilePath){ Remove-Item -Path $localFilePath -Force }
        }
    }
}
Catch {
    Write-Host -ForegroundColor Red "全局错误: $($_.Exception.Message)"
}
Finally {
    # 清理Excel COM对象
    if($excel){
        $excel.Quit()
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        Remove-Variable excel
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
    # 删除临时目录
    if(Test-Path -Path $TempFolder){
        Remove-Item -Path $TempFolder -Recurse -Force
    }
}

脚本说明 #

• 临时目录机制：将文件下载到本地修改，规避在线文件操作的权限与同步风险
• 正则转义处理：对URL中的特殊字符进行转义，确保替换操作准确无误
• COM对象清理：完善Excel对象的释放逻辑，避免内存泄漏
• 分级日志输出：用不同颜色区分日志类型，便于跟踪批量处理状态
• 容错设计：单个文件处理失败不中断整体任务，自动清理临时文件

内容的提问来源于stack exchange，提问作者Tristan Andrews