CODEWITHSUNDEEP
excelvba
lamwaiman1988
lamwaiman1988

Reputation: 3742

Is there a way to programmatically remove all vba module from a lot of excel worksheets

I have many excel file(.xls) in a folder and each of them have a vba macro/module in it and I need them to be remove. Can I write an external program which loop through the folder, inspect each excel file and remove vba module? Please tell me what programming language could does it most easily. Thanks!

With a manual process, I have to 1) open the worksheet, 2) click "Enable Content", 3) Go to Developer tab, 4)Click "Visual Basic" button, 5) Right click the vba module and delete. As I mentioned, I have of these excel files and it is a routine job and I want to remove the human part. Thanks.

Upvotes: 0

Views: 3923

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Change Path and try:

Option Explicit

Sub LoopThroughFiles()

    Dim StrFile As String
    Dim Element As Object
    Dim WB As Workbook

    StrFile = Dir("C:\Users\XXXXX\Desktop\Test\*")

    Do While Len(StrFile) > 0
        If Right(StrFile, 4) = ".xls" Then

            Set WB = Workbooks.Open("C:\Users\marios.p\Desktop\Test\" & StrFile)

            For Each Element In ActiveWorkbook.VBProject.VBComponents
                If Element.Type <> 100 Then
                    ActiveWorkbook.VBProject.VBComponents.Remove Element
                End If
            Next

            WB.Save
            WB.Close

        End If

        StrFile = Dir
    Loop

End Sub

Upvotes: 1

Lonolian
Lonolian

Reputation: 134

What I would do, I would create a macro which would open these files and save them as xlsx instead of xlsm. As xlsx can not contain the modules they will vanish. Than you just have to delete the original ones.

You can even use macro recording to do this.

If we are talking about a lot of files,( I suppose we do) You can generate the name of the files, if there is logic behind them ( like dates) or you can place them into folder, and than loop through the items in the folder, open them, save as, delete original.

You can find a lot of options how to check each file in a folder (FSO)

Happy new year!

Upvotes: 3

Related Questions