下面的代码可以批量设置001文件夹下所有XLS文件为“禁用宏就关闭文件”,但如果文件夹里面已经有设置好的文件,就会出错,请问如何用IF判断里面是否存在工作表"Macro",如果有就跳过这个文件,继续往下,如
If xs.Name = "Macro" Then
wb.Close True
Exit Sub
但我不知道如何加!
Sub test1() '批量设置禁用宏则关闭文件
Dim wb As Workbook
Dim i%
Dim err '变量声明
Application.ScreenUpdating = False '关闭屏幕更新,防止闪屏、加快代码运行
Application.DisplayAlerts = False
With Application.FileSearch '建立一个搜索
.NewSearch '新搜索
.LookIn = "C:\Documents and Settings\Administrator\桌面\001" '设定搜索的范围
.FileType = msoFileTypeExcelWorkbooks '查找所有Excel类型的文件
.Execute
For i = 1 To .FoundFiles.Count '逐个处理找到的文件列表中的每个工作簿
Set wb = Workbooks.Open(.FoundFiles(i)) '打开它
wb.Sheets.Add Type:=xlExcel4MacroSheet '插入宏表
wb.ActiveSheet.Name = "Macro" '命名宏表
Range("A2") = "=ERROR(TRUE,$A$6)" '在宏表中输入函数
Range("A4") = "=RETURN()"
Range("A6") = "=IF(ERROR.TYPE($A$3)=4)"
Range("A8") = "FILE.CLOSE(FALSE)"
Range("A9") = "RETURN()"
Range("A10") = "ELSE()"
Range("A11") = "ERROR(TRUE)"
Range("A12") = "RETURN()"
Range("A13") = "END.IF()"
wb.ActiveSheet.Visible = 2 '隐藏宏表
For Each xs In wb.Sheets '历遍工作表并定义名称
If xs.Type = xlWorksheet Then
xs.Select
ActiveWorkbook.Names.Add Name:=xs.Name & "!Auto_Activate", RefersToR1C1:="=Macro!R2C1"
End If
Next xs
wb.Close True '保存并关闭打开的工作簿
Next i '继续打开下一个工作簿
End With '结束With语句块
Application.ScreenUpdating = True '重新打开屏幕更新
Application.DisplayAlerts = True
MsgBox "已处理完毕!", 64, "提示"
End Sub