从文件夹中的所有工作簿中删除VBA代码 [英] Delete VBA code from all workbooks in a folder

问题描述

我正在尝试构建代码来远程循环使用.xls文件的文件夹，并删除其中包含的宏。到目前为止，我有单独的组件工作，但是在激活各种工作簿时遇到困难，然后以编程方式确保在每个文件中引用Microsoft Visual Basic for Application可扩展性5.3。

I am attempting to construct code to remotely loop through a folder with .xls files and delete the macros contained within. So far I have the individual components working, but am having difficulties activating the various workbooks and then programmatically ensuring "Microsoft Visual Basic for Application Extensibility 5.3" is referenced within each file.

谢谢！

Sub LoopFiles()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

strPath = ' enter path here

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)

For Each objfile In objFolder.Files


If objFso.GetExtensionName(objfile.Path) = "xls" Then
   Set Objworkbook = objExcel.Workbooks.Open(objfile.Path)
    ' Include your code to work with the Excel object here
    Objworkbook.Activate

    AddReference (objfile)

   Objworkbook.Close True 'Save changes
End If

Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub




Sub AddReference(FileRequired)

     FileRequired.Activate
     'MsgBox "Sheet: " & ActiveWorkbook.Name

     ActiveWorkbook.VBProject.References.AddFromGuid _
     GUID:="{0002E157-0000-0000-C000-000000000046}", _
     Major:=5, Minor:=3
End Sub




Sub DeleteAllVBACode()

        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Set VBProj = ActiveWorkbook.VBProject


        For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                Set CodeMod = VBComp.CodeModule
                With CodeMod
                    .DeleteLines 1, .CountOfLines
                End With
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
    End Sub

推荐答案

像我在评论中提到的，你不需要添加引用e到 Microsoft Visual Basic应用程序可扩展性5.3 从文件中删除代码。考虑这个小小的练习。

Like I mentioned in my comments, you do not need to add a reference to Microsoft Visual Basic for Application Extensibility 5.3 to delete the code from the files. Consider this small exercise.

1 ）。创建一个Excel文件

1). Create an Excel file

2 ）。将此代码粘贴到模块中

2). Paste this code in a module

Sub Sample1()
    MsgBox "A"
End Sub

3 ）。将上述文件保存为 C：\Sample.xls

3). Save the above file as C:\Sample.xls

4 ）。关闭文件

5 ）。打开一个新的excel文件并将该代码粘贴到一个模块中

5). Open a new excel file and paste this code in a module

Option Explicit

'~~> Trust Access To Visual Basics Project must be enabled.
Sub Sample2()
    Dim wb As Workbook
    Dim i As Long

    '~~> Replace this with the relevant file
    '~~> We can open the files in a loop as well
    Set wb = Workbooks.Open("C:\Sample.xls")

    On Error Resume Next
    With wb.VBProject
        '~~> Remove the components
        For i = .VBComponents.Count To 1 Step -1
            .VBComponents.Remove .VBComponents(i)
        Next i
        '~~> Remove the code lines
        For i = .VBComponents.Count To 1 Step -1
            .VBComponents(i).CodeModule.DeleteLines _
            1, .VBComponents(i).CodeModule.CountOfLines
        Next i
    End With
    On Error GoTo 0
End Sub

6 ）确保启用信任访问Visual Basics项目

6) Ensure that "Trust Access To Visual Basics Project" is enabled

7 ）运行 Sample2（）

您将看到 Sample.xls 被删除，我们甚至没有设置引用 Microsoft Visual Basic for Application Extensibility 5.3 。

You will see that the code in Sample.xls is deleted and we haven't even set the reference to Microsoft Visual Basic for Application Extensibility 5.3.

这篇关于从文件夹中的所有工作簿中删除VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！