Append Row in Excel if certain conditions Exist

Tagged: ,

This topic contains 8 replies, has 2 voices, and was last updated by Profile photo of Long S Long S 6 months, 3 weeks ago.

  • Author
    Posts
  • #38763
    Profile photo of Long S
    Long S
    Participant

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

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

  • #38768
    Profile photo of Paul Frankovich
    Paul Frankovich
    Participant

    I think it's your $todaysdate

    [DateTime]::Parse($(Get-Date)).ToString("MM/dd/yyyy")
    05/09/2016

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

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

  • #38771
    Profile photo of Long S
    Long S
    Participant

    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

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

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

  • #38773
    Profile photo of Paul Frankovich
    Paul Frankovich
    Participant

    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.

  • #38779
    Profile photo of Long S
    Long S
    Participant

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

  • #38788
    Profile photo of Paul Frankovich
    Paul Frankovich
    Participant

    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")
    $ExcelSh.Activate()
    
    $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()))
        {
            $true
            Break
        }
        Else
        {
            $false
        }
    }

    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

  • #38845
    Profile photo of Long S
    Long S
    Participant

    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

  • #38862
    Profile photo of Paul Frankovich
    Paul Frankovich
    Participant

    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.

  • #38928
    Profile photo of Long S
    Long S
    Participant

    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")
    $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 + 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
    }
    else{
    $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
    $ExcelWorkBook.SaveAs('file\test.xlsx')
    $ExcelWorkBook.Close()
    $Excel.Quit()
    [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

You must be logged in to reply to this topic.