Submitted By: Kent Finkle

Inserts blank rows in spreadsheets in order to group data by person.

PowerShell
Edit|Remove
$comments = @'
Script name: Use-BlankRow.ps1
Created on: Friday, July 13, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Use a Blank Row to Separate Data in an Excel Spreadsheet?
'@
#-----------------------------------------------------
function Release-Ref ($info) {
    foreach ( $p in $args ) { 
        ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
        [System.__ComObject]$p) -gt 0)
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers() 
    }    
}
#-----------------------------------------------------
$xlShiftDown = -4121
$xl = new-object -comobject excel.application
$xl.Visible = $True
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$array = new-object 'object[,]' 11,2
 
$array[0,0] = "A Person"
$array[0,1] = 2004
$array[1,0] = "A Person 1"
$array[1,1] = 2006
$array[2,0] = "A Person 2"
$array[2,1] = 2004
$array[3,0] = "B Person"
$array[3,1] = 2007
$array[4,0] = "B Person 1"
$array[4,1] = 2005
$array[5,0] = "B Person 2"
$array[5,1] = 2005
$array[6,0] = "B Person 3"
$array[6,1] = 2005
$array[7,0] = "C Person"
$array[7,1] = 2005
$array[8,0] = "C Person 2"
$array[8,1] = 2005
$array[9,0] = "D Person"
$array[9,1] = 2005
$array[10,0] = "E Person"
$array[10,1] = 2005
 
$r = $ws.Range("a1:b11")
$r.Value() = $array
$i = 1
$a = $ws.Cells.Item($i, 1).value()
$start = $a.substring(0,1)
 
Do {
    $a = $ws.Cells.Item($i, 1).value()
    $s = $a.substring(0,1)
    If ($s -ne $start) {
        $r = $ws.Cells.Item($i,1).EntireRow
        $a = $r.Activate()
        $a = $r.Insert($xlShiftDown)
        $a = $ws.Cells.Item($i + 1, 1).value()
        $start = $a.substring(0,1)
    }
    $i++
}
While ($ws.Cells.Item($i,1).Value() -ne $null)
 
$a = Release-Ref $ws $wb $xl