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

今天在两个地方看到了这样一个问题：把一个excel中的表复制出来另存为一个独立的excel文件。其中一个是cnblog，另一个想不起来了。想到自己将来也可能遇到这样的问题，于是用powershell做了一个脚本以备不时之需。

 

Powershell

当然，首先要有一个excel文件包括了一堆表。

用脚本自动化excel最常用的是COM object，下面的语句就是新建一个excel.application实例并把DisplayAlerts置为false，这样可以避开excel的告警。

$Excel = New-Object -ComObject Excel.Application$Excel.DisplayAlerts = $false

这其实就相当于我们双击了一下excel.exe，如果想把它显示出来只需要把$Excel.Visible属性置为true即可。

下面的语句就是打开目标excel文件，该open方法仅接受绝对路径。

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

现在我们已经有了workbook的对象，我们只需要对其中的Sheets做循环复制到一个新的Workbook即可。

$WorkBook.Sheets | %{    # 设置新的workbook的路径    $NewWorkBookPath = "$PWD\$($WorkBook.Name)_$($_.Name).xlsx"    # 新建一个workbook    $NewWorkBook = $Excel.Workbooks.Add()    # 把当前的sheet复制到这个新的workbook里面    $_.Copy($NewWorkBook.Sheets.Item(1))    # 默认新建一个workbook是有3个空的sheet，下面的语句是删掉这几个空的表    2..$NewWorkBook.Sheets.Count | %{        $NewWorkBook.Sheets.Item(2).Delete()    }    # 把该新的workbook另存为    $NewWorkBook.SaveAs($NewWorkBookPath) # close new workbook $NewWorkBook.Close() }

At last, close old workbook and excel.

最后，调用close方法关闭原来的workbook和excel

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

到这里可能有人注意到任务管理器里依然有一个excel.exe存在，实际上在其它的程序语言中也会遇到同样的问题，对于该问题的答案MS已经给出了解决办法。

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

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null

到这里工作就完成了，改良脚本就是之后的事了。

 

VBA

另一个方法是用office自带的宏来做这件事，打开excel按Alt + F11之后就能调用VBA editor，如果你不能的话可能是最开始安装office的时候没有选择宏组件。

VBA中的方法非常类似，只不过操作的对象不同罢了。

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 Next End Sub

这其实就是建了一个宏而已，在workbook界面用Alt + F8可以调用出宏界面执行它，或者在VBA editor界面按F5执行宏即可。

 - Larry