CODEWITHSUNDEEP
excelpowershell
Wayne Cui
Wayne Cui

Reputation: 835

Extract worksheets from Excel into separate files with PowerShell

I am trying to split an excel file with multi sheets to multi files, and searched this blog:Extract worksheets from Excel into separate files with PowerShell . I tried xls file, and it works fine. But when I tried xlsx file, it just output the whole source file, with each sheets as the default sheet when I opened the generated files with Excel Application. I am struggled with the solution but with no luck. My test code is like blow:

$Excel = New-Object -ComObject "Excel.Application" 
$Excel.Visible = $false #Runs Excel in the background. 
$Excel.DisplayAlerts = $false #Supress alert messages. 

$filepath ="D:\powershell\test.xlsx"
$Workbook = $Excel.Workbooks.open($filepath)
$WorkbookName = "test.xlsx"
$output_type = "xlsx"

if ($Workbook.Worksheets.Count -gt 0) { 
    write-Output "Now processing: $WorkbookName" 
    $FileFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook 

    $WorkbookName = $filepath -replace ".xlsx", "" 

    foreach($Worksheet in $Workbook.Worksheets) {
        $ExtractedFileName = $WorkbookName + "~~" + $Worksheet.Name + "." + $output_type 
        $Worksheet.SaveAs($ExtractedFileName, $FileFormat) 

        write-Output "Created file: $ExtractedFileName"
    }
} 

$Workbook.Close() 
$Excel.Quit()

Upvotes: 2

Views: 8655

Answers (1)

user4039065
user4039065

Reputation:

In keeping with standard Excel practise, copying a worksheet to no destination will create a new workbook with that worksheet as the solitary worksheet and that workbook will be the ActiveWorkbook.

$Excel = New-Object -ComObject "Excel.Application" 
$Excel.Visible = $false #Runs Excel in the background. 
$Excel.DisplayAlerts = $false #Supress alert messages. 

$filepath ="D:\powershell\test.xlsx"
$Workbook = $Excel.Workbooks.open($filepath)
$WorkbookName = "test.xlsx"
$output_type = "xlsx"

if ($Workbook.Worksheets.Count -gt 0) { 
    write-Output "Now processing: $WorkbookName" 
    $FileFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook 

    $WorkbookName = $filepath -replace ".xlsx", "" 

    foreach($Worksheet in $Workbook.Worksheets) {
        $Worksheet.Copy()
        $ExtractedFileName = $WorkbookName + "~~" + $Worksheet.Name + "." + $output_type 
        $Excel.ActiveWorkbook.SaveAs($ExtractedFileName, $FileFormat) 
        $Excel.ActiveWorkbook.Close

        write-Output "Created file: $ExtractedFileName"
    }
} 

$Workbook.Close() 
$Excel.Quit() 
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
Stop-Process -Name EXCEL
Remove-Variable Excel

Now that does propagate the workbook into multiple workbooks each with a copy of a single original worksheet. I've piled a couple of 'murder Excel' commands into the tail end of that in order to not end up with a rogue (and invisible) excel.exe sitting in the Task Manager's Processes tab.

There is an excellent thread on removing that last instance of excel.exe in How to Open; SaveAs; then Close an Excel 2013 (macro-enabled) workbook from PowerShell4.

Upvotes: 1

Related Questions