Submitted By: Kent Finkle

Auto-sizes columns in a Microsoft Excel spreadsheet.

PowerShell
Edit|Remove
$comments = @'
Script name: AutoFit-Columns.ps1
Created on: Friday, April 20, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to AutoFit Columns in a Microsoft Excel Worksheet?
'@

# -----------------------------------------------------
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) 
$x = 1

$sql = "Select * from Win32_Service"
$colItems = gwmi -query $sql
foreach ($objItem in $colItems) {
    $objWorksheet.Cells.Item($x, 1) = $objItem.Name
    $objWorksheet.Cells.Item($x, 2) = $objItem.DisplayName
    $objWorksheet.Cells.Item($x, 3) = $objItem.State
    $x++
}

$objRange = $objWorksheet.UsedRange
[void] $objRange.EntireColumn.Autofit()

$a = Release-Ref($objWorksheet)
$a = Release-Ref($objWorkbook)
$a = Release-Ref($objExcel)