Powershell Excel find value better performance alternative

Question

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)

You could start using jobs to make it so you have multiple excels searching multiple workbooks. — Matt
– Matt, Commented Mar 6, 2017 at 16:56
Thanks.... its the same workbook though, to which I am both reading and writing to... — AdilZ
– AdilZ, Commented Mar 6, 2017 at 17:14
Have you considered using the Worksheet.UsedRange range to exclude empty cells? — TheMadTechnician
– TheMadTechnician, Commented Mar 6, 2017 at 20:21
yes, and it takes 45 secs each loop query..... thats why I am stressed out lol.... I know for a fact that I just need to search 1 row... but even that takes so damn long — AdilZ
– AdilZ, Commented Mar 6, 2017 at 20:32
Could you take the where and put it outside the foreach altogether? Example: $worksheets = $WB.worksheets | where {$_.name -like "*$name*"} foreach ($ws in $worksheets) — Bewc
– Bewc, Commented Mar 6, 2017 at 20:55

Bewc · Accepted Answer · 2017-03-06 16:59:23Z

1

Anytime the pipeline is used, there's a performance hit. Instead of using the where object, try something like this (using an if statement):

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

Note that maybe your line has a typo for the part *where {$_.name -like "*$names*"}*. Maybe it should read *where {$_.name -like "*$name*"}*?

I found my basis from the following bookmark I had: http://community.idera.com/powershell/powershell_com_featured_blogs/b/tobias/posts/speeding-up-your-scripts

answered Mar 6, 2017 at 16:59

Bewc

4416 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheMadTechnician Over a year ago

That's not how you use the If statement. In order to use the If statement like you are suggesting there would need to be an additional loop nested within the ForEach loop that's already there. As it stands your If statement is malformed and missing its scriptblock.

AdilZ Over a year ago

So I removed the pipes, it didnt reduce it as much..rather when I piped it to Where {$cells.text -like "*$item*"}..it cut it down by a quarter, but not as much as reducing the range from the entire row to just 26 columns... which was practically 2 seconds.... however, this will grow, and fast, and when i tested with 100 columns, it took a whopping 15 secs per search... i really wish I dont have to spend 20 mins just to run a simple script....

Bewc Over a year ago

@TheMadTechnician Point taken. Thank you.

AdilZ · Accepted Answer · 2017-04-20 16:24:55Z

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)

Collectives™ on Stack Overflow

Powershell Excel find value better performance alternative

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related