<

Updating Excel Links in Thousands of Files

Published on
7,851 Points
651 Views
2 Endorsements
Last Modified:
Shaun Vermaak
My name is Shaun Vermaak and I have always been fascinated with technology and how we use it to enhance our lives and business.
Excel allows various different methods to link Excel files to each other. This includes relative paths, mapped drives (or the local drive) and UNC paths. UNC paths are the least robust of the three.

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!


2
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free