|
【概要】以表单为单位进行文件拆分。由多个表单组成的文件将按照表单为单位进行拆分,并保存。原文件原封不动保留下来。http://t.cn/RPOiJKK
- Private Sub CommandButton1_Click()
- On Error GoTo Err1
- '打开文件对话框
- selectfile = Application _
- .GetOpenFilename("Excel工作簿 (*.xls*), *.xls*", , "请指定要拆分的文件。")
- '.GetOpenFilename("Excel工作簿 (*.xls), *.xls", , "请指定要拆分的文件。")
- '保存最初路径
- biginpath = CurDir()
-
- '打开所选定的文件
- If selectfile <> False Then
- Workbooks.Open Filename:=selectfile
- Else
- Exit Sub
- End If
-
- '删除表单
- Dim ws As Worksheet
- Application.DisplayAlerts = False '---隐藏警告消息
- For Each ws In ActiveWorkbook.Worksheets
- If Application.WorksheetFunction.CountA(ws.UsedRange) = 0 Then
- ws.Delete '---删除未使用的表单
- End If
- Next ws
-
- Application.DisplayAlerts = True '---警告消息显示
- '指定另存为
- '打开文件对话框
- selectfolder = Application _
- .GetSaveAsFilename("选择场所", , , "指定另存为")
- myfolder = CurDir()
-
- If selectfolder <> False Then
- Application.ScreenUpdating = False '关闭界面切换
- '获取表名
- For Each n In Sheets
- newfilename = n.Name '获取表名
- n.Copy '把表单复制到新文件中
- pathname = myfolder & "" & newfilename '取得绝对路径
- ActiveSheet.PageSetup.PrintArea = "" '清除打印范围
- ActiveWorkbook.Close savechanges:=True, Filename:=pathname, routeworkbook:=False
- Next n
- ChDir biginpath
- MsgBox "成功!"
- End If
-
- '原文件结束
- ActiveWorkbook.Close savechanges:=False
- Application.ScreenUpdating = True '解除界面控制
- Exit Sub
-
- Err1:
- MsgBox "出错了。"
- End Sub
复制代码
该贴已经同步到 xiamen168的微博 |
|