分拆保存Excel多工作表

王志军2014-09-23 09:18应用 标签:工作 技巧

有不少Excel文件,里面保存了多个工作表,有时需要将其中的每个工作表提取出来,分别保存为单一的新工作簿文件。常规的方法是右击相关工作表,选择“移动或复制”,在“工作簿”下拉菜单中选择“新工作簿”,即可将当前工作表提取到新的工作簿中(图1)。注意,如果要在原工作簿中保留该工作表,需要勾选“建立副本”项。

 

1323crggzbfc1

但如果某个工作薄内有着比较多的工作表,那么按照上述方法逐一保存,操作起来相当麻烦。其实,我们还可以通VBA来批处理实现。

下面方法的优点:

1.将包含多工作表的工作簿快速分拆为独立的工作簿文件

2.分拆时可以自由选择.xls或xlsx格式

右键单击任意一个工作表标签,从弹出菜单中选择“查看代码”,此时会进入Microsoft Visual Basic for Applications窗口,依次选择“插入→模块”,插入一个新的模块,再在模块对话框输入相应代码。

VBA中的模块语句为:

Private Sub 模块名()

End Sub

在这两行之间加入实际的操作命令(图2),其实在本例中,除了Dim之类的变量定义外,实际的操作代码也就一行:

1323crggzbfc2

ActiveWorkbook.SaveAs Filename:=MyBook.Path & "\" & sht.Name, FileFormat:=xlNormal

通过For语句循环该命令,将所有的工作表自动提取保存下来。ActiveWorkbook.Close是每循环一次的关闭操作,对提取并无实质意义。

代码输入检查无误,从“运行”菜单下选择“运行子过程/用户窗体”,或者按下F5功能键,稍等片刻,会弹出“文件已经被拆分完毕”的提示框,我们可以在当前路径下发现分拆完成的工作簿文件,文件名即原工作簿的各个工作表标签名(图3),很方便吧。

1323crggzbfc3

进阶篇:

需要指出的是,执行上述代码之后,所获得的新工作簿文件都是.xls格式,如果需要分拆为.xlsx格式,只要将“xlNormal”修改为“xlOpenXMLWorkbook”即可。修改代码之后,并不局限于原工作簿文件是否属于.xlsx格式,即使是.xls格式的工作簿,也可以强制分拆为一个一个的.xlsx工作簿文件。如果修改为“xlExcel8”,可以将工作表强制分拆为.xls格式的工作簿文件,也就是说利用这一功能可以实现工作簿格式的快速转换。