CODEWITHSUNDEEP
excelloopspowershell
user3700864
user3700864

Reputation: 1

Finding a certain name on excel using Powershell (using loops or not)

I have trouble running a loop in Excel down a list in Excel (Rows), using Powershell. I am trying to scan for certain name, using $var for search in excel. Can you help me fix the problem. thanks.

$var="410043"

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible= $False
$WorkBook = $objExcel.Workbooks.Open("C:\Users\step.k\Desktop\Copy of TEST24.xlsx")
#$WorkBook.sheets | Select-Object -Property Name
$Workbook.Worksheets.Item("sheet1").Activate()

$Range = $Workbook.Range("B1: B14492").EntireRow  
$Search = $Range.find($var)  
$var

Upvotes: 0

Views: 123

Answers (1)

user4039065
user4039065

Reputation:

The following .PS1 will use Range.Find/Range.FindNext methods to list all of the occurances

$filePath = "T:\TMP\find_410043.xlsx"
$var = "410043"
$xl = New-Object -ComObject Excel.Application
$xl.Visible = $true
if (test-path $filePath) {
$wb = $xl.Workbooks.Open($filePath)
$ws = $wb.WorkSheets.item("sheet1")

$rc1 = $ws.Range("B1:B14492").entirerow.find($var)
if ($rc1) 
    {
    $found = 1
    $addr = $rc1.address(0, 0, 1, 0)
    do
        {
        $rc1 = $ws.cells.findnext($rc1)
        write-host $rc1.address(0, 0, 1, 0)
        } until ($addr -eq $rc1.address(0, 0, 1, 0))
    }
}
$wb.close()
$xl.quit()

Note that the first written to the command window is actually the second occurrence. The first is listed last and then the address matches so the loop is exited. If there is only one then there is no difference.

Upvotes: 1

Related Questions