<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Updating Excel Links in Thousands of Files

Published on
7,596 Points
396 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 48

Author Comment

by:Shaun Vermaak
Thank you Fanie
0

Featured Post

Check Out How Miercom Evaluates Wi-Fi Security!

It's not just about Wi-Fi connectivity anymore. A wireless security breach can cost your business large amounts of time, trouble, and expense. Plus, hear first-hand from Miercom on how WatchGuard's Wi-Fi security stacks up against the competition plus a LIVE demo!

Join & Write a Comment

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Discover the basics of using Outlook 2016 from office 365.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month