Updating Excel Links in Thousands of Files

Introduction

I found myself in a situation where thousands of Excel files had UNC (Universal Naming Convention) cross-links to each other with the name of a server that was decommissioned.

For some reason, probably security related, adding a CNAME (Canonical Name) that resolved the old server name to the new server name didn't work. Using NETDOM to add the old server name as an alternative name was also not an option because the new server could not be restarted without organizing downtime.

I tried various commercial tools, such as PowerGrep. Not only are these expensive, the ones I tried cannot replace a value in a function, only the resulting value.

I knew XLSX files are basically a ZIP file with special headers; so, playing around I managed to get a Powershell replace function going.

Dependency

Download and extract 7za.exe into %WINDIR%\System32 folder so that it is available from any folder

https://www.7-zip.org/download.html

Powershell

Run this to import the Update-ExcelLinks function:

function Update-ExcelLinks($xlsxFile, $oldText, $newText) {
    # Build BAK file name
    $bakFile =  $xlsxFile -ireplace [regex]::Escape(".xlsx"), ".bak"

    # Build ZIP file name
    $zipFile =  $xlsxFile -ireplace [regex]::Escape(".xlsx"), ".zip"

    # Create temporary folder
    $parent = [System.IO.Path]::GetTempPath();
    [string] $guid = [System.Guid]::NewGuid();
    $tempFolder = Join-Path $parent $guid;
    New-Item -ItemType Directory -Path $tempFolder;

    # Uncomment the next line to create backup before processing XLSX file
    # Copy-Item $xlsxFile $bakFile

    # Rename file to ZIP
    Rename-Item -Path $xlsxFile -NewName $zipFile

    # Not using Expand-Archive because it changes the ZIP format
    7za.exe x "$zipFile" -o"$tempFolder"

    # Replace old text with new text
    $fileNames = Get-ChildItem -Path $tempFolder -Recurse -Include *.xml,*.xml.rels
    foreach ($file in $fileNames)
    {
        (Get-Content -ErrorAction SilentlyContinue $file.PSPath) |
        Foreach-Object { $_ -replace $oldText, $newText } |
        Set-Content $file.PSPath
    }

    # Changing working folder because 7Zip option -w doesn't work
    Set-Location -Path $tempFolder

    # Not using Compress-Archive because it changes the ZIP format
    7za.exe u -r "$zipFile" *.*

    # Rename file back to XLSX
    Rename-Item -Path $zipFile -NewName $xlsxFile
}

Demo Execution

To update values in Excel file, use the following command:

Update-ExcelLinks THEEXCELFILE.xlsx OLDTEXT NEWTEXT

Problem solved!

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts...

Please do not forget to press the "Thumb's Up" button if you think this article was helpful and valuable for EE members.

It also provides me with positive feedback. Thank you!