[POWERSHELL/VBA] separating tables in Excel into separate Excel files

This article also published in Eng @ http://www.cnblogs.com/LarryAtCNBlog/p/4441201.html

Today, in two places, you see a problem: Copy a table from Excel and save it as a standalone Excel file. One of them is cnblog, and the other can't remember. Thinking that he might encounter such problems in the future, he used PowerShell to make a script to prepare for a rainy day.

Powershell

Of course, the first thing to have an Excel file is to include a bunch of tables.

Using scripting to automate Excel is the most commonly used COM object, the following statement is to create a new Excel.Application instance and set DisplayAlerts to False, which avoids the Excel alarm.

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

This is actually the equivalent of double-clicking on the Excel.exe, if you want to show it, just set the $excel.visible property to True.

The following statement opens the target Excel file, which only accepts absolute paths.

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

Now that we have the workbook object, we just need to make a loop copy of the sheets to a new workbook.

$WorkBook. Sheets | %{    # Sets the path of the new WorkBook    $NewWorkBookPath = "$PWD \$ ($WorkBook. Name) _$ ($_. Name). xlsx "    # Create a new workbook    $NewWorkBook = $Excel. Workbooks.Add ()    # Copy the current sheet into the new workbook    $_. Copy ($NewWorkBook. Sheets.item (1))    # Default New workbook is 3 empty sheet, the following statement is to delete these empty tables    2..$ NewWorkBook.Sheets.Count | %{        $NewWorkBook. Sheets.item (2). Delete ()    }    # Save the new workbook as    $NewWorkBook. SaveAs ($NewWorkBookPath) # Close New Workbook $ NewWorkbook. Close ()}      

At last, close old workbook and Excel.

Finally, call the Close method to close the original workbook and Excel

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

It may be noted here that there is still a Excel.exe in the Task manager, and in fact the same problem is encountered in other programming languages, and the answer to this question, MS, has given a solution.

Https://technet.microsoft.com/en-us/library/ff730962.aspx

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

The work is done here, and the improved script is the next thing.

Vba

Another way is to use the office-brought macro to do this, open Excel press ALT + F11 after you can call the VBA editor, if you can not be the first to install Office when the macro component is not selected.

The methods in VBA are very similar, except that they operate in different objects.

Sub splitsheets ()    application.displayalerts = False for each    Sheet in Sheets        Newworkbookpath = Activeworkbook.fullname & "_" & Sheet.name & ". xlsx"        Set w = workbooks.add        sheet.copy W. Sheets.item (1) for        i = 2 to w.sheets.count            W.sheets.item (2). Delete Next w.saveas newworkbookpath w.close Set w = Nothing nextend Sub      

This is actually built a macro, in the workbook interface with ALT + F8 can call out the macro interface to execute it, or in the VBA editor interface Press F5 to execute the macro.

-Larry

[POWERSHELL/VBA] separates tables in Excel into separate Excel files