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()
Jonathan Nelson
  • 11
  • 3
  • 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 Answers1

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.

smwk
  • 570
  • 2
  • 5
  • 14
  • 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 03 '16 at 18:24
  • You can import the Excel document and then make any relinking changes you need and finally export to Excel. See https://github.com/dfinke/ImportExcel/blob/master/images/TryImportExcel.gif ImportExcel is just to make importing and exporting quicker, it won't do anything else. – smwk Aug 03 '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 04 '16 at 20:34
  • It imports to a psobject that can be manipulated and then exported again. – smwk Aug 04 '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 08 '16 at 21:22
  • You'll have to give more details than that. I did a test import and was able to change a hyperlink and then export it again. – smwk Aug 09 '16 at 08:41
  • I'm referring to links to separate Excel files. This is a different feature than Hyperlinks. ImportExcel seems to be lacking a wide variety of features, including importing all worksheets at the same time or preserving macros. Also, I'm getting this error which has been left unaddressed for a year. I can't even use Export-Excel. https://github.com/dfinke/ImportExcel/issues/44 – Jonathan Nelson Aug 10 '16 at 16:20
  • I didn't have those issues with importing/exporting but I doubt it supports preserving macros. Unfortunately I don't know of any alternatives, using com to access Excel is slow no matter what you do. – smwk Aug 10 '16 at 23:56