16,554
社区成员
发帖
与我相关
我的任务
分享
Module Sheet_Split
Sub Sheet_Split() '工作表拆分
Dim Gress As New From1 ' 实例化一个进度条
Dim Ex As Excel.Application
'Try '检查错误
Dim Sheet As Excel.Worksheet
Dim Newbook As Excel.Workbook '新建工作簿的工作表
Dim SelectPath As String '选择保存的文件夹路径
Dim Column_index As Short '条件判断列的列号
Ex = Globals.ThisAddIn.Application
Ex.DisplayAlerts = False '关闭弹窗提醒,提醒按默认按钮处理
Dim value As Short = 0 '进度条值
Dim If_Column = Ex.InputBox("请选择条件列", "请选择", "A", Type:=8) '用于条件判断的列
If If_Column.Equals(False) Then
MsgBox("你没有选择正确的列,不会进行拆分",, "注意")
Exit Sub
End If
Sheet = Ex.ActiveSheet '获取活动工作表
Column_index = If_Column.Column '提取列号
Ex.ScreenUpdating = False '关闭前台刷新 在选择框后关闭,防止看不到选择区域时的虚线
Dim Folder As New Windows.Forms.FolderBrowserDialog '创建一个文件夹选择框
Folder.ShowNewFolderButton = True '允许在对话框中直接新建文件夹
If Folder.ShowDialog <> 1 Then
MsgBox("你没有选择保存路径!不会进行拆分",, "注意")
Exit Sub
End If
SelectPath = Folder.SelectedPath & "\"
Folder = Nothing '释放对象
Dim Area = Sheet.UsedRange.Columns(Column_index).Value '获取条件列所有值
Dim dic As New Dictionary(Of String, String) '创建一个字典
Dim val As String
Stop
For Each val In Area '使用字典去重复
If val <> "" Then
dic.Item(val) = Nothing
End If '字典关键词不可为空。工作簿命名不可为空。
Next
'If dic.Count > 100 Then
' MsgBox("拆分文件不可超出100个",, "警告")
' Exit Sub
'End If
Gress.Show() '显示进度条
For Each val In dic.Keys
value += 1
Gress.ProgressBar1.Value = (value / dic.Keys.Count) * 100 '进度条值
Gress.Text = "进度:" & CByte((value / dic.Keys.Count) * 100) & "%" '进度条百分比
Sheet.UsedRange.AutoFilter(Column_index, val,,, False) '自动筛选 并因此筛选按钮
Newbook = Ex.Workbooks.Add()
Sheet.Cells.Copy(Newbook.ActiveSheet.Range("A1")) '拷贝筛选后的部分
Newbook.SaveAs(SelectPath & val & ".xlsx") '另存为
Newbook.Close() '关闭文件
Next
Sheet.AutoFilterMode = False '取消该工作表的筛选
Shell("Explorer " & SelectPath, AppWinStyle.NormalFocus) '打开保存的文件夹,并正常显示
Gress.Close() '关闭进度条
Ex.ScreenUpdating = True '开启前台刷新
Ex.DisplayAlerts = True '开启弹窗提醒
'Catch e As Exception
' Gress.Close()
' Ex.ScreenUpdating = True '开启前台刷新
' Ex.DisplayAlerts = True '开启弹窗提醒
' MsgBox(ex.ToString)
' Exit Sub
'End Try
End Sub '工作表拆分
End Module
Module Sheet_Split
Sub Sheet_Split() '工作表拆分
Dim Gress As New From1 ' 实例化一个进度条
Dim Ex As Excel.Application
'Try '检查错误
Dim Sheet As Excel.Worksheet
Dim Newbook As Excel.Workbook '新建工作簿的工作表
Dim SelectPath As String '选择保存的文件夹路径
Dim Column_index As Short '条件判断列的列号
Ex = Globals.ThisAddIn.Application
Ex.DisplayAlerts = False '关闭弹窗提醒,提醒按默认按钮处理
Dim value As Short = 0 '进度条值
Dim If_Column = Ex.InputBox("请选择条件列", "请选择", "A", Type:=8) '用于条件判断的列
If If_Column.Equals(False) Then
MsgBox("你没有选择正确的列,不会进行拆分",, "注意")
Exit Sub
End If
Sheet = Ex.ActiveSheet '获取活动工作表
Column_index = If_Column.Column '提取列号
Ex.ScreenUpdating = False '关闭前台刷新 在选择框后关闭,防止看不到选择区域时的虚线
Dim Folder As New Windows.Forms.FolderBrowserDialog '创建一个文件夹选择框
Folder.ShowNewFolderButton = True '允许在对话框中直接新建文件夹
If Folder.ShowDialog <> 1 Then
MsgBox("你没有选择保存路径!不会进行拆分",, "注意")
Exit Sub
End If
SelectPath = Folder.SelectedPath & "\"
Folder = Nothing '释放对象
Dim Area = Sheet.UsedRange.Columns(Column_index).Value '获取条件列所有值
Dim dic As New Dictionary(Of String, String) '创建一个字典
Dim val As String
Stop
For Each val In Area '使用字典去重复
If val <> "" Then
dic.Item(val) = Nothing
End If '字典关键词不可为空。工作簿命名不可为空。
Next
'If dic.Count > 100 Then
' MsgBox("拆分文件不可超出100个",, "警告")
' Exit Sub
'End If
Gress.Show() '显示进度条
For Each val In dic.Keys
value += 1
Gress.ProgressBar1.Value = (value / dic.Keys.Count) * 100 '进度条值
Gress.Text = "进度:" & CByte((value / dic.Keys.Count) * 100) & "%" '进度条百分比
Sheet.UsedRange.AutoFilter(Column_index, val,,, False) '自动筛选 并因此筛选按钮
Newbook = Ex.Workbooks.Add()
Sheet.Cells.Copy(Newbook.ActiveSheet.Range("A1")) '拷贝筛选后的部分
Newbook.SaveAs(SelectPath & val & ".xlsx") '另存为
Newbook.Close() '关闭文件
Next
Sheet.AutoFilterMode = False '取消该工作表的筛选
Shell("Explorer " & SelectPath, AppWinStyle.NormalFocus) '打开保存的文件夹,并正常显示
Gress.Close() '关闭进度条
Ex.ScreenUpdating = True '开启前台刷新
Ex.DisplayAlerts = True '开启弹窗提醒
'Catch e As Exception
' Gress.Close()
' Ex.ScreenUpdating = True '开启前台刷新
' Ex.DisplayAlerts = True '开启弹窗提醒
' MsgBox(ex.ToString)
' Exit Sub
'End Try
End Sub '工作表拆分
End Module