CODEWITHSUNDEEP
excelpowershellautomation
ShanayL
ShanayL

Reputation: 1247

Append Row in Excel if certain conditions Exist

I am trying to use a comparison to find the row in an excel spreadsheet and modify it if the date is the same as todays date and the Domain is the same as the one entered.

I can t seem to find an article to help me find out how to locate the cells to compare them. Once the row is matched I will then append some of the cells. My excel looks something like this:

Domain, Succeeded, Failed, As Of
ABC, 245, 12, 5/9/2012
NET, 7778, 25, 8/3/2013
ABC, 454, 44, 5/9/2016
NET, 54,90, 5/9/2016

So my code will prompt for a domain. The user will enter ABC. The code sees the row with matching ABC and 5/9/2016 and add how many succeeded and how may failed to the appropriate column in that row. I can get the addition part I just need help finding out how to parse the excel file.

I will eventually make this code human error proof but for the purpose of getting help parsing. This is what I have.

$excelfilepath = 'file\test.xlsx'

$Excel = New-Object -ComObject Excel.Application
$ExcelWorkBook = $Excel.Workbooks.Open($excelfilepath)
$ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
$ExcelWorkSheet.activate()

$Domain= Read-Host "What is the Domain Name"
$succeeded = Read-Host "How many succeeded"
$failed = Read-Host "How many failed"
$todaysdate = Get-Date
$todaysdate= [DateTime]::Parse($todaysdate).ToString("MM/dd/yyyy")

$lastRow = $ExcelWorkSheet.UsedRange.rows.count

$row = $ExcelWorkSheet.UsedRange.Rows | where {$_.Value2 -eq $Domain} | select Value2

With that I get 

Value2,
-------
{ABC, 245, 12, SomeDecimal...}
{ABC, 454, 44, 42499...}

modification (maybe??)

$Row = $ExcelWorkSheet.UsedRange.Rows | where {$_.Cells.Item(1).Value2 -eq $Enviornment -and $_.Cells.Item(4).value2 -eq $todaysdate}
$Row.Cells.Item(2).value2 = $succeeded + $Row.Cells.Item(2).value2

Upvotes: 0

Views: 315

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36342

You're going to want to specify the cell to compare here. Something like:

$row = $ExcelWorkSheet.UsedRange.Rows | where {$_.Cells.Item(1).Value2 -eq $Domain -and $_.Cells.Item(4).value2 -eq $todaysdate}

That gets that row as a Range object. Then you modify the cells in the row you want like:

$Row.Cells.Item(2).value2 = $succeeded + $Row.Cells.Item(2).value2

Edit: Where statement breakdown. Sure, we can go over that. Here's just that statement:

where {$_.Cells.Item(1).Value2 -eq $Domain -and $_.Cells.Item(4).value2 -eq $todaysdate}

I assume that the confusion comes from $_.Cells.Item(1).Value2. What this does is looks at all of the rows in the UsedRange, and for each row it looks at the cells in that row, and of those cells it specifies the first item (being the cell in Column A). It then checks to see if the value in that cell matches $Domain. If it does it moves on to the next part of the Where statement, where it specifies that the 4th cell in the row (the cell in Column D), and checks to see if the value of that cell matches $todaysdate.

Your modification... You have specified two parameters in the Item() method, and I don't think it takes two parameters. Can you try the code as I specified, and then let me know if you get an error using it, or if it is not doing what you want?

Upvotes: 1

Related Questions