![]() |
Excel spreadsheet file. |
First thing is to set some variables specific to my environment, the path and name to both my input (xlsx) and output (csv) files, and the name of the tab or worksheet in the spreadsheet.
$Sheet = "CreateVM-CA" $xls = "C:\Temp\PC\PCTest.xlsx" $csv = "C:\Temp\PC\$Sheet.csv"
NOTE: Make sure you save your Excel file as this script reads the file from disk & not what's in an open workbook.
Next we will open Excel, the workbook, and the specific worksheet we want to export. I'm also using "Excel.Visible = $False" to prevent the Excel GUI from opening, and "Excel.DisplayAlerts = $False" to prevent messages such as being prompted to overwrite the file if it already exists (see below).
$objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $False $objExcel.DisplayAlerts = $False $WorkBook = $objExcel.Workbooks.Open($xls) $WorkSheet = $WorkBook.sheets.item("$Sheet")
![]() |
Prompted to replace existing file (when not using DisplayAlerts=$False) |
$WorkBook.sheets | Select-Object -Property Name
![]() |
Listing Excel worksheets with PowerShell. |
$xlCSV = 6 $WorkBook.SaveAs($csv,$xlCSV)
Closing/quitting Excel. Initially I was using just the first command to quit Excel. However, I discovered that it remained open and subsequent executions of my script would fail to write the file because the open Excel process had the file locked. So after a little research and testing I found the second command which kills the process, and prevents the CSV file from being locked.
$objExcel.quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
This little script works beautifully now and does just what I need by opening a specific worksheet from an Excel workbook, then saving it as a CSV file. Here's the entire script.
$Sheet = "CreateVM-CA" $xls = "C:\Temp\PC\PCTest.xlsx" $csv = "C:\Temp\PC\$Sheet.csv" $objExcel = New-Object -ComObject Excel.Application $objExcel.Visible = $False $objExcel.DisplayAlerts = $False $WorkBook = $objExcel.Workbooks.Open($xls) $WorkSheet = $WorkBook.sheets.item("$Sheet") $xlCSV = 6 $WorkBook.SaveAs($csv,$xlCSV) $objExcel.quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
![]() |
Output CSV file. |