vba实现excel自动化
2024年1月29日大约 2 分钟约 724 字
一、背景
checklist中有多个工作表,对每个工作表筛选出fail项进行查看,费时费力
二、实现思路
在excel中编写VBA脚本,遍历每个表并进行筛选失败项,将fail项汇总到failResult表中
三、具体实现
Attribute VB_Name = "fail项过滤"
<!-- more -->
'Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
Sub 宏1()
'添加注释
Application.ScreenUpdating = False
'
Application.DisplayAlerts = False
' test2 宏
'Dim sheetCount As Integer
Dim resultSheetName As String
Dim searchResultLocation As String
'Dim sheetCopyRange As String
'Dim sheetPasteRange As String
Dim judge, currentSheetName As Excel.Worksheet, s$
'Dim colmunsCount As Integer
'Dim rowsCount As Integer
'fail result name
Dim FRN As String
'判断是否存在failResult文件夹
FRN = "failResult"
s = "failResult"
Set judge = CreateObject("Scripting.Dictionary")
For Each currentResultName In Worksheets
judge(currentResultName.Name) = ""
Next
If judge.exists(s) Then
GoTo begin
Else
'MsgBox "请创建一个名为failResult的工作表"
Sheets.Add After:=ActiveSheet
Sheets(2).Select
Sheets(2).Name = FRN
End If
Set judge = Nothing
begin:
'新建一个sheet存放失败项
'获取脚本数量
' sheetCount = Worksheets.CountNew
For Each currentSheetName In ThisWorkbook.Worksheets
If currentSheetName.Name = FRN Then
GoTo nexttime
End If
'判断表是否为空
If ShIsBlank(currentSheetNajme) = True Then
currentSheetName.Delete
GoTo nexttime
End If
Sheets(currentSheetName.Name).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Select
ActiveSheet.UsedRange.AutoFilter
' MsgBox currentSheetName.Name
'获取表名字
fname = ActiveSheet.Name
'搜索并确定result位置
ActiveSheet.Cells.Find(What:="Result", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, MatchByte:=False, SearchFormat:=False).Activate
'等待一段时间再执行
'Application.Wait Now + TimeValue("00:00:01")
searchResultLocation = ActiveCell.Address(0, 0)
'CONSTCOLUMNS = Left(searchResultLocation, 1) + "800"
Selection.EntireColumn.Select
'对选中数据进行筛选
Selection.AutoFilter
ActiveSheet.Range(searchResultLocation).End(xlDown).AutoFilter Field:=1, Criteria1:="Fail"
'Application.Wait Now + TimeValue("00:00:01")
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 3).Select
'ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).Select
Sheets(currentSheetName.Name).Range("a1").CurrentRegion.Select
'复制粘贴到新的表
Selection.Copy
Sheets(FRN).Select
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 2).Value = Sheets(currentSheetName.Name).Name + ":"
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row).Interior.ColorIndex = 3
'到最后一格空格处复制粘贴防止覆盖
Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'Application.Wait Now + TimeValue("00:00:01")
'Rows(ActiveSheet.UsedRange.Rows.Count).Insert
ActiveSheet.Paste
'Range("a" & Cells(Rows.Count, 1).End(xlUp).Row + 1).Select
'colmunsCount = ActiveSheet.UsedRange.Columns.Count
'rowsCount = ActiveSheet.UsedRange.Rows.Count
'ActiveSheet(failResult).Range().Values = currentSheetName
'选择有效数据
nexttime:
Next
MsgBox "fail项均已在failResult表中"
End Sub
Function ShIsBlank(sht As Variant) As Boolean
If TypeName(sht) = "string" Then
Set sht = Worksheets(sht) 'sht是工作表对象,完全可以作为Worksheets集合的参数加以利用.
End If
If Application.WorksheetFunction.CountA(sht.UsedRange.Cells) = 0 Then
ShIsBlank = True
End If
End Function四、使用方法
4.1 使用前准备文档
《fail项过滤.bas》
4.2 打开待过滤的excel文件:
1.alt+F11 2.ctr+m 导入文件fail项过滤.bas 3.找到右上角模块->双击 fail项过滤 4.F5
4.3 备注(出问题查阅):
1.执行出错则删除failResult表重新执行一次
2.有的表内存在合并单元格可能导致程序运行失败,可选择手动筛选含合并单元格的fail项 然后删除该sheet再执行
3.若过滤的result包含fail项以外的内容,可手动删除。fail项才是需要处理的内容
