<

Updating Excel Links in Thousands of Files

Published on
5,438 Points
238 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
Comment
2 Comments
 

Expert Comment

by:Fanie Prinsloo
Confirmed.  The process works like a dream and saved us days of work trying to update the links manually within the files
1
 
LVL 43

Author Comment

by:Shaun Vermaak
Thank you Fanie
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Learn how to collaborate with office 365 Office Online

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month