Hi,

I often have to collect some data and fill out some excel lists with those. Here are a few examples how to control Excel using the COM object with powershell.

###############################################################################
# This script controls Excel remotely
# Author Michael Albert michlstechblog.info
# License: GPL v2
################################################################################
# File must exists
$sInputFile="D:\temp\exceldocument.xlsx"
# Get COM Object
$oExcel = New-Object -ComObject "Excel.Application"
# Set Excel visible
$oExcel.Visible = $true
# open excel file
$oExcelDoc = $oExcel.Workbooks.Open($sInputFile)
# Open 1st Worksheet
$oWorksheet = $oExcelDoc.Worksheets.item(1)
# Jump to worksheet (only GUI)
$oWorksheet.Activate()
# Get Rows and columns dimension
$iUsedRangeRow  = $oWorksheet.UsedRange.Rows.Count
$iUsedRangeColumns  = $oWorksheet.UsedRange.Columns.Count
# Auto adjust column with
$oWorksheet.UsedRange.EntireColumn.AutoFit()|out-null
# Read a cell A1 A=1 1=1, Row and columns startet by 1
write-host "Cell A1" $oWorksheet.Cells.Item(1,1).Text
# Write text
$oWorksheet.Cells.Item(2,1).value2="New Text"
# Activate cell (only GUI)
$oWorksheet.Cells.Item(1,1).Activate()|out-null
# Set Backgroundcolor of a cell
$oWorksheet.Cells.Item(1,1).Interior.ColorIndex = 4
# With RBG, first load assembly
[reflection.assembly]::LoadWithPartialName("System.Drawing")
# With .NET defined color constants
$oWorksheet.Cells.Item(2,1).Interior.Color=[System.Drawing.Color]::White.ToARgb()
# With  RBG Color
$oWorksheet.Cells.Item(2,1).Interior.Color=([System.Drawing.Color]::FromArgb(254,7,7)).ToArgb()
# Set Font Color
$oWorksheet.Cells.Item(2,1).Font.ColorIndex = 1
# Font Size
$oWorksheet.Cells.Item(2,1).Font.Size=12
# Rotate
$oWorksheet.Cells.Item(2,1).Orientation=45
# Bold
$oWorksheet.Cells.Item(2,1).Font.Bold = $true
# Strike through
$oWorksheet.Cells.Item(2,1).Font.Strikethrough=$true
# Write a comment to a cell
$oWorksheet.Cells.Item(2,1).AddComment("This is a comment")|Out-Null

# Format Cell Range
$oWorksheet.Range("A1:B3").Numberformat="00,0"
$oWorksheet.Cells.Item(2,1).NumberFormat="@"
$oWorksheet.Cells.Item(2,1).NumberFormatLocal="@"

# New Worksheet and get object
$oNewWorksheet=$oExcelDoc.Worksheets.add()
# Rename new Worksheet
$oNewWorksheet.Name="My new worksheet"

# Close Document and save
$oExcelDoc.Close($true)
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oWorksheet)|out-null
$oWorksheet=$null
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcelDoc)|out-null
$oExcelDoc=$null
# Close Excel
$oExcel.Quit()
Start-Sleep 1
# Cleanup COM
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($oExcel)|out-null
$oExcel=$null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

To be continued 🙂

Michael