1

We are doing a shared network drive reorganization. This will break links in hundreds of gigs of Excel files that the business uses. In order to soften the blow, I've created a Powershell script that automatically relinks these files. However, it runs super slow at only about 1 MB a minute. It would take several months to relink all Excel files. For now, I am asking the business to restrict the amount of data they are requesting for relinking, however, the faster I can get my script to run, the happier the business will be. It's currently perfectly functional, just slow. Does anyone have any advice for how I can speed up this Powershell script? Any feedback would be appreciated.

$object = New-Object -comObject Shell.Application  
$folder = $object.BrowseForFolder(0, 'Select the folder where your Excel files are located', 0)

$oldname = "oldname"
$newname = "newname"

if (!$folder -or !$oldname -or !$newname) {exit} 

$excel = New-Object -comObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

foreach ($file in Get-ChildItem -literalPath $folder.self.Path*.xls?) {
$workbook = $excel.Workbooks.Open($file.fullname)

foreach ($link in $workbook.LinkSources(1))
{
    write-host Old link: $link
    $newlink = $link -ireplace [regex]::Escape($oldname), $newname
    if (Test-Path $newlink) {
        write-host New link: $newlink
        $workbook.ChangeLink($link,$newlink,1)
    }
    else {
        write-host New link does not exist: $newlink
    }
}
$workbook.Save()
$workbook.Close()
}
$excel.quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers() 
  • 1
    Who is expected to run this? You or your users? The easiest way to speed this up is to split the file list into several parts, and then run the script several times simultaneously with one part each. While I could criticize your code style (e.g., the last 4 lines are pointless, and you call [regex]::Escape($oldname) every iteration of the inner loop instead of once) none of that is touching the source of slowness: Excel itself and PowerShell running as a single thread. – Bacon Bits Jul 28 '16 at 17:01
1

Have a look at the ImportExcel module. It can import and export data from Excel directly so it is a lot quicker and reliable than using COM objects.

  • Thank you for your suggestion. I looked into using that module, but it doesn't appear to support features like relinking files. Please let me know if you think I may have overlooked something. – Jonathan Nelson Aug 3 '16 at 18:24
  • You can import the Excel document and then make any relinking changes you need and finally export to Excel. See github.com/dfinke/ImportExcel/blob/master/images/… ImportExcel is just to make importing and exporting quicker, it won't do anything else. – smwk Aug 3 '16 at 19:32
  • ImportExcel appears to load the contents of cells only as far as I can tell. I don't see any properties to display links or methods to manipulate links like in the COM object. – Jonathan Nelson Aug 4 '16 at 20:34
  • It imports to a psobject that can be manipulated and then exported again. – smwk Aug 4 '16 at 22:41
  • Right, but it doesn't import 100% of the content. The content I want to manipulate is not imported. – Jonathan Nelson Aug 8 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.