Submitted By: Kent Finkle

Saves an Excel spreadsheet as an XML file.

PowerShell
Edit|Remove
$comments = @'
Script name: Convert-ExcelToXML.ps1
Created on: Sunday, April 29, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to
Convert an Excel Spreadsheet to XML?
'@

#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 
}
#-----------------------------------------------------

$xlXMLSpreadsheet = 46
$xlFalse = 0

$objExcel = new-object -comobject excel.application
$objExcel.Visible = $True

$objWorkbook = $objExcel.Workbooks.Add()
$objWorksheet = $objWorkbook.Worksheets.Item(1)

$k = 1

for ($i = 1; $i -le 5; $i++) {
    for ($j = 1; $j -le 3; $j++) {
        $objWorksheet.Cells.Item($i,$j).Value() = $k
        $k++
    }
}

$objExcel.DisplayAlerts = $xlFalse
$a = $objWorkbook.SaveAs("C:\Scripts\Test.xml", $xlXMLSpreadsheet)

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