CODEWITHSUNDEEP
powershell-3.0
VivekRR
VivekRR

Reputation: 15

Excel scanning using powershell

I want to scan the second column of an excel file and if the value is "zero", it should display the first column value in the same row.How to write a powershell script

Upvotes: 0

Views: 1495

Answers (1)

Bryan
Bryan

Reputation: 3451

$File = "$pwd\test.xlsx"

#Setup Excel, open $File and set the the first worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$Worksheet = $Workbook.Worksheets.Item(1)

$SearchString = "Some Value"

$Range = $Worksheet.Range("A1").EntireColumn
$Search = $Range.find($SearchString)

$Search.value() = "Another Value"

The line:

$Range = $Worksheet.Range("A1").EntireColumn

sets a range for the entire A column. If you prefer, you can specify a fixed range like so:

$Range = $Worksheet.Range("A1","A5")

We then set the output from the find method to the $Search variable in this line:

$Search = $Range.find($SearchString)

We simply pass the search string, in this case $SearchString, as a parameter in the find method on a NamedRange object. Note that the returned value will be $null if the search does not find a match.

And, finally, we change the value of the found cell:

$Search.value() = "Another Value"

There are also FindNext and Find Previous methods that can be called if you want to find all instances.

Source: Here - Further Reading Here & Here

Upvotes: 1

Related Questions