Submitted By: Kent Finkle
Assigns values to cells based on background color, and then sums these assigned values.
$comments = @' Script name: Sum-ByColor.ps1 Created on: Tuesday, May 01, 2007 Author: Kent Finkle Purpose: How can I use Windows Powershell to Assign a Background Color to Cells in a Spreadsheet and Then "Sum" Those Cells? '@ # ----------------------------------------------------- function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } # ----------------------------------------------------- $objExcel = new-object -comobject excel.application $objExcel.Visible = $True $objWorkbook = $objExcel.Workbooks.Add() $objWorksheet = $objWorkbook.Worksheets.Item(1) $objWorksheet.Cells.Item(1, 1).Value() = "A" $objWorksheet.Cells.Item(2, 1).Value() = "B" $objWorksheet.Cells.Item(3, 1).Value() = "C" $objWorksheet.Cells.Item(4, 1).Value() = "D" $objWorksheet.Cells.Item(5, 1).Value() = "E" $objWorksheet.Cells.Item(6, 1).Value() = "F" $objWorksheet.Cells.Item(7, 1).Value() = "G" $objWorksheet.Cells.Item(8, 1).Value() = "H" $objWorksheet.Cells.Item(1, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(2, 1).Interior.ColorIndex = 8 $objWorksheet.Cells.Item(3, 1).Interior.ColorIndex = 9 $objWorksheet.Cells.Item(4, 1).Interior.ColorIndex = 10 $objWorksheet.Cells.Item(5, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(6, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(7, 1).Interior.ColorIndex = 8 $objWorksheet.Cells.Item(8, 1).Interior.ColorIndex = 10 $i = 1 Do { $intColor = $objExcel.Cells.Item($i, 1).Interior.ColorIndex switch ($intColor) { 7 {$intSum += 5} 8 {$intSum += 10} 9 {$intSum += 15} 10 {$intSum += 20} } $i++ } While ($objWorksheet.Cells.Item($i,1).Value() -ne $null) Write-Host($intSum) $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel)
$comments = @' Script name: Sum-ByColor.ps1 Created on: Tuesday, May 01, 2007 Author: Kent Finkle Purpose: How can I use Windows Powershell to Assign a Background Color to Cells in a Spreadsheet and Then "Sum" Those Cells? '@ # ----------------------------------------------------- function Release-Ref ($ref) { ([System.Runtime.InteropServices.Marshal]::ReleaseComObject( [System.__ComObject]$ref) -gt 0) [System.GC]::Collect() [System.GC]::WaitForPendingFinalizers() } # ----------------------------------------------------- $objExcel = new-object -comobject excel.application $objExcel.Visible = $True $objWorkbook = $objExcel.Workbooks.Add() $objWorksheet = $objWorkbook.Worksheets.Item(1) $objWorksheet.Cells.Item(1, 1).Value() = "A" $objWorksheet.Cells.Item(2, 1).Value() = "B" $objWorksheet.Cells.Item(3, 1).Value() = "C" $objWorksheet.Cells.Item(4, 1).Value() = "D" $objWorksheet.Cells.Item(5, 1).Value() = "E" $objWorksheet.Cells.Item(6, 1).Value() = "F" $objWorksheet.Cells.Item(7, 1).Value() = "G" $objWorksheet.Cells.Item(8, 1).Value() = "H" $objWorksheet.Cells.Item(1, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(2, 1).Interior.ColorIndex = 8 $objWorksheet.Cells.Item(3, 1).Interior.ColorIndex = 9 $objWorksheet.Cells.Item(4, 1).Interior.ColorIndex = 10 $objWorksheet.Cells.Item(5, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(6, 1).Interior.ColorIndex = 7 $objWorksheet.Cells.Item(7, 1).Interior.ColorIndex = 8 $objWorksheet.Cells.Item(8, 1).Interior.ColorIndex = 10 $i = 1 Do { $intColor = $objExcel.Cells.Item($i, 1).Interior.ColorIndex switch ($intColor) { 7 {$intSum += 5} 8 {$intSum += 10} 9 {$intSum += 15} 10 {$intSum += 20} } $i++ } While ($objWorksheet.Cells.Item($i,1).Value() -ne $null) Write-Host($intSum) $a = Release-Ref($objWorksheet) $a = Release-Ref($objWorkbook) $a = Release-Ref($objExcel)