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
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.
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}
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")
$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
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”)
$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