|
楼主 |
发表于 2018-12-24 16:01
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- 'http://club.excelhome.net/thread-1302700-3-1.html
- Sub 同夹_批量删除特定的工作表_A()
- Application.ScreenUpdating = False: Application.DisplayAlerts = False
- 路径 = ThisWorkbook.Path & "": 外薄 = Dir(路径 & "*.xls")
- Do While 外薄 <> ""
- If 外薄 <> ThisWorkbook.Name Then
- If 子程序(路径 & 外薄) Then
- m = m + 1
- With Workbooks.Open(路径 & 外薄)
- For Each 工作表 In .Worksheets
- If 工作表.Name <> "岁段名册总表" And 工作表.Name <> "在校生花名册" Then
- n = n + 1
- 工作表.Delete
- End If
- Next 工作表
- .Close True
- End With
- End If
- End If
- 外薄 = Dir
- Loop
- Application.DisplayAlerts = True: Application.ScreenUpdating = True
- If m > 0 Then
- MsgBox "已处理" & m & "个工作簿,删除了" & n & "个工作表", vbInformation
- Else
- MsgBox "没有发现需要删除工作表的工作簿。", vbInformation
- End If
- End Sub
- Function 子程序(ByVal MyPath$) As Boolean
- Dim 连接 As Object, 记录 As Object, s$
- Set 连接 = CreateObject("ADODB.Connection")
- 连接.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 4.0;Data Source=" & MyPath
- Set 记录 = 连接.OpenSchema(20)
- Do Until 记录.EOF
- If 记录.Fields("TABLE_TYPE") = "TABLE" Then
- s = Replace(记录("TABLE_NAME").Value, "'", "")
- If s <> "岁段名册总表$" And s <> "在校生花名册$" Then
- 子程序 = True
- Exit Do
- End If
- End If
- 记录.MoveNext
- Loop
- 记录.Close: Set 记录 = Nothing
- 连接.Close: Set 连接 = Nothing
- End Function
- Sub 同夹_批量删除特定的工作表_E()
- 路径 = ThisWorkbook.Path & "": 外薄 = Dir(路径 & "*.xls")
- Application.DisplayAlerts = False: Application.ScreenUpdating = False
- If MsgBox("是否确定要删除指定目录当中指定工作表" & Chr(13) & "请做好数据备份,一旦删除,无法恢复!", vbQuestion + vbYesNo, "重要提醒") = vbYes Then
- Do While 外薄 <> ""
- If 外薄 <> ThisWorkbook.Name Then
- Set 打开的外薄 = Workbooks.Open(路径 & 外薄)
- For Each 工作表 In 打开的外薄.Sheets
- If 工作表.Name <> "岁段名册总表" And 工作表.Name <> "在校生花名册" Then 工作表.Delete
- Next 工作表
- 打开的外薄.Save
- 打开的外薄.Close False
- End If
- 外薄 = Dir
- Loop
- Else
- MsgBox "您已取消操作,数据未删除!"
- End If
- Application.DisplayAlerts = True: Application.ScreenUpdating = True
- End Sub
- Sub 多级_多夹_批量删除特定的工作表_ADO加子程序()
- Application.ScreenUpdating = False: Application.DisplayAlerts = False '//关闭系统提示
- FileArr = 子程序(ThisWorkbook.Path, "*.xls?", ThisWorkbook.Name, True, False)
- For I = 0 To UBound(FileArr)
- Set 打开的外薄 = Workbooks.Open(FileArr(I))
- For Each 工作表 In 打开的外薄.Worksheets
- If 工作表.Name <> "岁段名册总表" And 工作表.Name <> "在校生花名册" Then '//
- 工作表.Delete
- End If
- Next 工作表
- 打开的外薄.Close True
- Next I
- Application.ScreenUpdating = True: Application.DisplayAlerts = True '//恢复系统提示
- End Sub
- Public Function 子程序(ByVal Filename As String, Optional ByVal FileFilter As String = "*.*", Optional ByVal Liwai As String = "", Optional ByVal SubFiles As Boolean = True, Optional ByVal Files As Boolean = False) As String()
- Dim 字典, 关键字, MyName, MyFileName
- Dim I As Integer
- Set 字典 = CreateObject("Scripting.Dictionary")
- Filename = Replace(Replace(Filename & "", "\", ""), "\", "")
- 字典.Add (Filename), ""
- I = 0
- Do While I < 字典.Count
- 关键字 = 字典.keys '开始遍历字典
- If SubFiles = True Then '//如果需要查找子文件夹
- MyName = Dir(关键字(I), vbDirectory) '查找目录
- Do While MyName <> ""
- If MyName <> "." And MyName <> ".." Then
- If (GetAttr(关键字(I) & MyName) And vbDirectory) = vbDirectory Then '如果是次级目录
- 字典.Add (关键字(I) & MyName & ""), "" '就往字典中添加这个次级目录名作为一个条目
- End If
- End If
- MyName = Dir '继续遍历寻找
- Loop
- End If
- I = I + 1
- Loop
- Dim arrx() As String
- I = 0
- If Files = True Then '//是否只输出文件夹名
- For Each 关键字 In 字典.keys '以查找总表所在文件夹下所有excel文件为例
- ReDim Preserve arrx(I)
- If 关键字 <> Filename Then '//自身文件夹除外
- arrx(I) = 关键字
- I = I + 1
- End If
- Next
- 子程序 = arrx
- Else
- For Each 关键字 In 字典.keys '以查找总表所在文件夹下所有excel文件为例
- MyFileName = Dir(关键字 & FileFilter) '过滤器:EXCEL2003为:*.xls,excel2007为:*.xlsx
- Do While MyFileName <> ""
- If MyFileName <> Liwai Then '排除例外文件
- ReDim Preserve arrx(I)
- arrx(I) = 关键字 & MyFileName
- I = I + 1
- End If
- MyFileName = Dir
- Loop
- Next
- 子程序 = arrx
- End If
- End Function
复制代码 |
|