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.

here is what I have

$excelfilepath = 'file\test.xlsx'

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

$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")

$row = ($ExcelWorkSheet.UsedRange.Rows| ?  { ($_.Value2 | ? {$_.'As of' -eq $todaysdate}) -AND ($_.'Domain' -eq $Domain)}).Row

I think it’s your $todaysdate


Which dose not equal 5/9/2016 in a test of text vs. text

I was just thinking the date format M/d/yyyy should do what you need.

nothing returns when I call $row
Im not sure if this is done right
$row = ($ExcelWorkSheet.UsedRange.Rows| ? { ($.Value2 | ? {$.‘As of’ -eq $todaysdate}) -AND ($_.‘Domain’ -eq $Domain)}).Row

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

I get
{ABC, 245, 12, 5/9/2012}
{ABC, 454, 44, 5/9/2016}

Only input I can add on this is that Value2 is an array that you would have to expand to get the individual values out and then Value2 doesn’t know that the fourth value is supposed to mean ‘As Of’ value.

Sorry, that exhaust my ability to help with Excel. I typically work in CSVs and just manually save them back to XLSX before I send them back to people.

yeah…same here…this is a new avenue for me

So, I’ve worked on this a bit today and this is what I came up with for matching cells to certain conditions:

$ExcelFilePath = "$pwd\Temp.xlsx"

$Excel = New-Object -ComObject Excel.Application
$ExcelWB = $Excel.Workbooks.Open($ExcelFilePath)
$ExcelSh = $Excel.Worksheets.Item("Sheet1")

$Domain = Read-Host "What is the Domain name"

$TodaysDate = [DateTime]::Parse($(Get-Date)).ToString("M/d/yyy")

#Here comes the new stuff
#Find how many rows in the sheet
$Rows = $ExcelSh.UsedRange.Rows.Count

#Loop through the rows checking only column A and D since we know
#that is where our target data is, starting on row 2 to skip
#the headers
For($Row = 2; $Row -le $Rows; $Row++)
    If (($Domain -eq $ExcelSh.Range("A$Row").Text.Trim()) `
    -and `
    ($TodaysDate -eq $Excelsh.Range("D$Row").Text.Trim()))

Probably a better way of doing this, but this is what I came up after a little bit of Google. Good luck with getting it all put together.

PS: Backticks are only so it will fit in the PRE block. I don’t use them personally

oh wow… thanks for the effort. doesn’t look like it worked though… im still researching and have came up with something to the effect of

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

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

Mine only gives you the row number. After the For loop breaks you are left with the current value of $Row. Since you know that column B is succeeded and column C is failed, you just have to add those to the row number you still have in $Row.

This is most certainly a learning experience for both of us here. I’d love to see how you finally figure this one out.

I figured it out here is the full code:

$excelfilepath = ‘file\test.xlsx’

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

$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 + 1
[void] ($Excel.Range(“A” + $lastrow).Activate())

#looks through each row until a match is made
$Row = $ExcelWorkSheet.UsedRange.Rows | where {($.Cells.Item(1).Value2 -eq $Domain) -and ($.Cells.Item(4).Value() -eq $todaysdate)}

#if there is no matching row then one is created on the bottom of the data ($lastrow)
#if there is a matching row it is updated.
If ($Row -eq $null)
$Excel.Cells.Item($LastRow, 1).Value2 = $Domain
$Excel.Cells.Item($LastRow, 3).Value2 = $failed
$Excel.Cells.Item($LastRow, 2).Value2 = $succeeded
$Excel.Cells.Item($LastRow, 4).Value2 = $todaysdate
$Row.Cells.Item(2).value2 = $Row.Cells.Item(2).value2 + $succeeded
$Row.Cells.Item(3).value2 = $Row.Cells.Item(3).value2 + $failed

#Save and Close File
$excel.DisplayAlerts = $false
$excel.ScreenUpdating = $false
$excel.Visible = $false
$excel.UserControl = $false
$excel.Interactive = $false
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Stop-Process -Name EXCEL -Force

So I ended up using the statements:
this is the comparison statement
$Row = $ExcelWorkSheet.UsedRange.Rows | where {($.Cells.Item(1).Value2 -eq $Domain) -and ($.Cells.Item(4).Value() -eq $todaysdate)}

This is the statement that adds the new amount to the row called in the comparison
$Row.Cells.Item(2).value2 = $Row.Cells.Item(2).value2 + $succeeded
$Row.Cells.Item(3).value2 = $Row.Cells.Item(3).value2 + $failed