Submitted By: Kent Finkle

Copies column C from one worksheet to column A of a second worksheet.

PowerShell
Edit|Remove
$comments = @'
Script name: Copy-Column.ps1
Created on: Friday, May 04, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to Copy Column C of One Worksheet to Column A of a Second 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.Open("C:\Scripts\Test.xls")

$objWorksheet = $objWorkbook.Worksheets.Item(2) 
$a = $objWorksheet.Activate()

$objRange = $objWorkSheet.Range("C1").EntireColumn
$a = $objRange.Copy()

$objWorksheet = $objWorkbook.Worksheets.Item(1) 
$a = $objWorksheet.Activate()

$objRange = $objWorkSheet.Range("A1")
$objWorksheet.Paste($objRange)

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