CODEWITHSUNDEEP
excelpowershell
Oxycash
Oxycash

Reputation: 197

Searching a single Excel column for a keyword returns only the first cell details

I have an excel sheet with two colums. Col A is a list of hostnames. Col B says either True OR False. I am using below script to search for FALSE and get respective cell row and column numbers. But it returns only the very first cell which contains FALSE and ends there. Do I need to tell it to loop/recurse or something like that?

Heres my code:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $False
$workbook = $excel.Workbooks.Open("C:\temp\extest3.xlsx")
$sheet = $workbook.ActiveSheet

$getStatus = $sheet.UsedRange.find("False")
$cellrow = $getStatus.Row
$cellcol = $getstatus.Column
$celladdress = $cellrow,$cellcol
$celladdress 
$workbook.Save()
$workbook.Close()

The output I get is only one cell row and col number. Not getting other cells which contains false.

Upvotes: 0

Views: 322

Answers (2)

user6811411
user6811411

Reputation:

While it is possible to use the Excel Com Object with PowerShell as AdminOfThings good answer shows,
it is (especially for simple flat data structures) quite clumsy and requires Excel to be installed locally.

I suggest to use Doug Finkes famous ImportExcel module.

Just for demonstration create a sample .xlsx file:

# generate test file with random data
1..10|ForEach-Object{
    [PSCustomObject]@{
        HostName = 'Test{0:D2}' -f $_
        Online   = [bool](0..1|Get-Random)
    }
} | Export-Excel .\extest3.xlsx

HostName Online
-------- ------
Test01    False
Test02    False
Test03     True
Test04     True
Test05     True
Test06    False
Test07    False
Test08     True
Test09     True
Test10     True

And output the HostNames with False in the next column what I presume is what you want:

Import-Excel .\extest3.xlsx | 
  Where-Object {-not $_.Online} | 
    Select-Object -ExpandProperty HostName

Test01
Test02
Test06
Test07

Upvotes: 3

AdminOfThings
AdminOfThings

Reputation: 25031

You can continue using the Find() method with the After parameter. A while loop with a break statement can be used to halt the search once you return to the top of the sheet.

$getStatus = $sheet.UsedRange.Find("False")
$firstrow = $getStatus.Row
$firstcol = $getstatus.Column
$celladdress = $firstrow,$firstcol
$celladdress

while ($getStatus) {
    $getStatus = $sheet.UsedRange.Find("False",$getStatus)
    $cellrow = $getStatus.Row
    $cellcol = $getstatus.Column
    $celladdress = $cellrow,$cellcol
    if ($celladdress[0] -eq $firstrow) {
        break
    }
    $celladdress
}

The value of the After parameter is always $getStatus.

Upvotes: 2

Related Questions