[POWERSHELL/VBA] To split Excel sheets to individual workbooks.

1 week ago I still in a wonderful city for business trips, that's a great trip I been through so far. So great for the city and my colleagues, I still remember it from time to time~

Ok, today I inadvertently saw some posts regarding to split excel sheets to workbooks, I thought someday I'll face the S Ame situation in the future from others. So, I gave it a shot to achieve in PowerShell and VBA, as a solution for my future customers.

Powershell

First of all, we had a xlsx file contains several sheets, our goal here are to save each sheet into a workbook.

To access Excel on script, the most of the used is COM object. So let's create one, of course set alert as false would release us from many Excel alerts.

$Excel = new-object-comobject excel.application$Excel$false

This basicly means I opened a excel.exe, if I want to see it, there are a property $Excel. Visible, set it to true you'll See.

Then, I use below code to open target Excel file, Beaware the "Workbooks.Open ()" method is only accept full path.

$WorkBook $Excel. Workbooks.Open ("$PWD \all.xlsx")

Now $WorkBook. Sheets contains all Sheets, all we need to do are loops each sheet and setup a new WorkBook to copy original S Heet.

$WorkBook. Sheets | %{    #Setup New workbook path and name    $NewWorkBookPath="$PWD \$ ($WorkBook. Name) _$ ($_. Name). xlsx"    #Add new workbook in Excel    $NewWorkBook=$Excel. Workbooks.Add ()#Copy Sheet to the new workbook    $_. Copy ($NewWorkBook. Sheets.item (1))    #New Workbook always has 3 blank sheets by default and below code to remove them2..$NewWorkBook. Sheets.count | %{        $NewWorkBook. Sheets.item (2). Delete ()}#Save the new workbook to file    $NewWorkBook. SaveAs ($NewWorkBookPath)    #Close New Workbook    $NewWorkBook. Close ()}

At last, close old workbook and Excel.

$WorkBook . Close ()$Excel. Quit ()

But, things does over yet, some might notice there are a excel.exe process still stays in Task manager even we closed powers Hell. In other programme languages would encounter the same problem, the "the" to quiet Excel.exe are described in HTTPS://TECHNET.M Icrosoft.com/en-us/library/ff730962.aspx

[System.runtime.interopservices.marshal]::releasecomobject ($Excel) | Out-null

Til now all jobs do, more developments to make the script better are not in the scope, so finished by now.

Vba

Another-achieve the goal is VBA, open a Excel and press ALT + F11 you can open VBA Editor, if you can ' t you must fo Rgot Intall it when you install Office.

The the on VBA is quite same like PowerShell, actually just with different objects.

Subsplitsheets () application.displayalerts=False     for  eachSheetinchSheets Newworkbookpath= Activeworkbook.fullname &"_"& Sheet.name &". xlsx"        SetW =workbooks.add sheet.copy W.sheets.item (1)         fori =2  toW.sheets.count W.sheets.item (2). DeleteNextw.saveas Newworkbookpath w.closeSetW = Nothing    NextEnd Sub

It's just a macro, in workbook view with Alt + F8 can quick invoke macro commands, in the VBA editor view, just press F5.

-Larry

[POWERSHELL/VBA] To split Excel sheets to individual workbooks.