Search code examples
excelperformancepowershellcom

Powershell Excel find value better performance alternative

I have to go through a loop in excel using the COM Object (no additional modules allow in environment aside from what comes installed with POSH 5).

In each loop I have to look through a worksheet (from a list of variables) for a particular set of values and pull and append data according to it.

My problem isnt so much accomplishing it, but rather the performance hit i get every time I do a Find Value2 in each worksheet.

With future expected massive increase of list of worksheets, and old ones with just more and more columns to parse through and work on in the future, how can I make this smoother and faster.

What I currently do is the following:

 $Exl = New-Object -ComObject "Excel.Application"
 $Exl.Visible = $false
 $Exl.DisplayAlerts = $false
 $WB = $Exl.Workbooks.Open($excel)

Foreach ($name in $names) {
 $ws = $WB.worksheets | where {$_.name -like "*$name*"}
 $range = $ws.Range("C:C")
 $findstuff = $range.find($item)
 $stuffrow = $findstuff.row
 $stuffcolumn = $findstuff.column

  }

This last part is what takes A LOT of time, and with each additional sheet and more columns I only see it growing, where it might take 10-20 mins

what can be done to optimize this?

On a side note: while I only need the one row and columnar results, there is also a slight issue with when finding value, it only shows the first result. If in the future there might be a need for the multiple rows and columns where value2 = $variable what should I do? (thats less important though, I asked in case if its related)

Solution

  • So I found a very simple answer.... which is somehow simultaneously EXTREMELY obvious and EXTREMELY unintuitive.

    When defining the $range variable Add a pipe to select ONLY the stuff you need.

    Instead of:

     $range = $ws.Range("C:C")

    do:

      $range = $ws.Range("C:C") | Select Row, text, value2, column

    Why is this unintuitive?

    1) Normally Piping would make things slower especially if your pushing many to filter a few

    2) One would expect that, especially since its going through the COM object, since it ACTUALLY runs the action when setting a variable rather than just defining. But that is not what happens here. When you set the Variable, it runs AFTER the variable has been defined and gathers the data THE MOMENT the variable is called [I tested this, and saw resource usage at that particular period only], and saves the data after that first variable call. (WHICH IS VERY WEIRD)