title: Powershell Excel find value better performance alternative
body: <p>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).</p>
  
  <p>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.</p>
  
  <p>My problem isnt so much accomplishing it, but rather the performance hit i get every time I do a Find Value2 in each worksheet.</p>
  
  <p>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.</p>
  
  <p>What I currently do is the following:</p>
  
  <pre><code> $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
  
    }
  </code></pre>
  
  <p>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 </p>
  
  <p>what can be done to optimize this?</p>
  
  <p>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 = <strong>$variable</strong>  what should I do? (thats less important though, I asked in case if its related)</p>