Submitted By: Kent Finkle

Collects data from an Excel spreadsheet and then saves that data to a text file.

PowerShell
Edit|Remove
$comments = @'
Script name: Paste-ColumnToTextFile.ps1
Created on: Tuesday, May 08, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to Select a Column of Data in Excel and Then Paste that Data into a Text File?
'@

# -----------------------------------------------------
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(1) 
$a = $objWorksheet.Activate

$i = 1

Do {
    $strValue = $objWorksheet.Cells.Item($i,3).Value()
    If ($strValue -ne $null) {
        $strText += $strValue
        $strText += "`r"
        $i++
    }
}
While ($objWorksheet.Cells.Item($i,3).Value() -ne $null)

$a = $objExcel.Quit()

Set-Content "C:\Scripts\ExcelData.txt" $strText

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