Solved

VBA Excel - Remove all modules from Workbook after export

Posted on 2010-08-31
12
668 Views
Last Modified: 2012-06-21
Hi,

I have a function to export all modules to a set location on my local PC.  The export works fine.  What I would like now, is a function to remove all the modules after I have exported them.  I have some code, but it does not work, it gives me the following error:
Invalid procedure call or argument on line :
 pVBAProject.VBComponents.Remove pVBAProject.VBComponents.Item(i)

Any ideas?
Sub RemoveVBAFiles()

    Dim pVBAProject As VBProject

    Dim vbComp As VBComponent

    Dim i As Integer

    Dim n As Integer

    

    ' Get the VBA project

   ' Set pVBAProject = ThisWorkbook.VBProject

    Set pVBAProject = Application.Workbooks("Oz_main.xls").VBProject

    n = pVBAProject.VBComponents.Count

    For i = n To 1 Step -1

        Set vbComp = pVBAProject.VBComponents.Item(i)

        Select Case vbComp.Type

        Case vbext_ct_StdModule

            If vbComp.Name <> "Module1" Then

                pVBAProject.VBComponents.Remove pVBAProject.VBComponents.Item(i)

            End If

        Case Else

            If vbComp.Name <> "ThisWorkbook" And vbComp.Name <> "Sheet1" Then

                pVBAProject.VBComponents.Remove pVBAProject.VBComponents.Item(i)

            End If

        End Select

    Next

    MsgBox "Sheet has been cleared of all modules"

End Sub

Open in new window

0
Comment
Question by:NerishaB
  • 6
  • 5
12 Comments
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
hi try something like that:

pVBAProject.VBComponents.Remove pVBAProject.VBComponents(i)

however you cannot remove Sheet modules that way as they are bound to the workbook sheets.
0
 

Author Comment

by:NerishaB
Comment Utility
Thanks, I tried that.  Can you help me with a function to remove all modules?
0
 
LVL 3

Expert Comment

by:WiB
Comment Utility
I guess this should work:

On Error Resume Next
    For Each prj In Application.VBE.VBProjects
        If prj.Name = "MyProject" Then  -> your project name here
            On Error Resume Next
            For Each vbComp In prj.VBComponents
                Select Case vbComp.Type
                .......
                    Application.VBE.VBProjects("MyProject").VBComponents.Remove vbComp
                End If
            Next vbCom
        End If
    Next prj
0
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
If thats the case then try that:
Sub RemoveVBAFiles()

    Dim pVBAProject As VBProject

    Dim vbComp As VBComponent

    Dim i As Integer

    Dim n As Integer

    

    ' Get the VBA project

    Set pVBAProject = ThisWorkbook.VBProject

    'Set pVBAProject = Application.Workbooks("Oz_main.xls").VBProject

    n = pVBAProject.VBComponents.Count

    For i = n To 1 Step -1

        Set vbComp = pVBAProject.VBComponents.Item(i)

        Select Case vbComp.Type

        Case vbext_ct_StdModule

            If vbComp.Name <> "Module1" Then

                vbComp.Remove pVBAProject.VBComponents(i)

            End If

        Case Else

            If vbComp.Name <> "ThisWorkbook" And vbComp.Name <> "Sheet1" Then

                On Error Resume Next

                vbComp.CodeModule.DeleteLines 1, vbComp.CodeModule.CountOfLines

                On Error GoTo 0

            End If

        End Select

    Next

    MsgBox "Sheet has been cleared of all modules"

End Sub

Open in new window

0
 

Author Comment

by:NerishaB
Comment Utility
Thanks Smax:
The problem is this: I have 2 workbooks open, and I only want to remove all the modules from 1 of the workbooks, thats why I used the name of the specific Workbook.  I need to make sure that it removes all modules from the specific Workbook that I want.
0
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
I've used ThisWorkbook for testing purposes but you can use commented code refering to other workbook.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:NerishaB
Comment Utility
I have tried the code you gave me, as well as this code below.  I dont get any errors, and I receive the message : "Sheet has been cleared of all modules", but none of the modules are removed.  Any other ideas?
Sub RemoveVBAFiles()

    Dim pVBAProject As VBProject

    Dim vbComp As VBComponent

    Dim i As Integer

    Dim n As Integer

 

    For Each pVBAProject In Application.VBE.VBProjects

    If pVBAProject.FileName = "C:\Netserver\oztech_dev_oz\oz_excel\oz_main.xls" Then

    On Error Resume Next

      For Each vbComp In pVBAProject.VBComponents

        Select Case vbComp.Type

        Case vbext_ct_StdModule

            If vbComp.Name <> "Module1" Then

                Application.VBE.VBProjects("Oz_main.xls").VBComponents.Remove vbComp

            End If

        Case Else

            If vbComp.Name <> "ThisWorkbook" And vbComp.Name <> "Sheet1" Then

                On Error Resume Next

                Application.VBE.VBProjects("Oz_main.xls").VBComponents.Remove vbComp

            End If

        End Select

        Next vbComp

        End If

    Next

    MsgBox "Sheet has been cleared of all modules"

End Sub

Open in new window

0
 
LVL 7

Expert Comment

by:MSmax
Comment Utility
Sheet modules can not be removed but the code inside of it will be cleared. Check it plese.
0
 

Author Comment

by:NerishaB
Comment Utility
I checked, the code still remains.  See attached picture.  The modules you see in the picture is what I am trying to remove.
modules.png
0
 

Author Comment

by:NerishaB
Comment Utility
I got another bit of code off the net.  I dont know how to call this though. Can you please help.  See code below:
Sub RemoveVBAFiles(objDocument As Object)

    Dim i As Long, l As Long

    If objDocument Is Nothing Then Exit Sub

    i = 0

    On Error Resume Next

    i = objDocument.VBProject.VBComponents.Count

    On Error GoTo 0

    If i < 1 Then ' no VBComponents or protected VBProject

        MsgBox "The VBProject in " & objDocument.Name & _

            " is protected or has no components!", _

            vbInformation, "Remove All Macros"

        Exit Sub

    End If

    With objDocument.VBProject

        For i = .VBComponents.Count To 1 Step -1

            On Error Resume Next

            .VBComponents.Remove .VBComponents(i)

            ' delete the component

            On Error GoTo 0

        Next i

    End With

    With objDocument.VBProject

        For i = .VBComponents.Count To 1 Step -1

            l = 1

            On Error Resume Next

            l = .VBComponents(i).CodeModule.CountOfLines

            .VBComponents(i).CodeModule.DeleteLines 1, l

            ' clear lines

            On Error GoTo 0

        Next i

    End With

End Sub

Open in new window

0
 
LVL 7

Accepted Solution

by:
MSmax earned 500 total points
Comment Utility
Try this code, which should remove all the modules excluding Module1, Sheet1, ThisWorkbook and delete code from all Sheet modules from 2 onwards.

Only thing needed to make this code work is to have the oz_main file open.
Sub RemoveVBAFiles()

    Dim pVBAProject As VBProject

    Dim vbComp As VBComponent

    Dim i As Integer

    Dim n As Integer

 

    For Each pVBAProject In Application.VBE.VBProjects

        If pVBAProject.Filename = "C:\Netserver\oztech_dev_oz\oz_excel\oz_main.xls" Then

            For Each vbComp In pVBAProject.VBComponents

                Select Case vbComp.Type

                    Case vbext_ct_StdModule

                        If vbComp.Name <> "Module1" Then

                            pVBAProject.VBComponents.Remove vbComp

                        End If

                    Case Else

                        If vbComp.Name <> "ThisWorkbook" And vbComp.Name <> "Sheet1" Then

                            On Error Resume Next

                                pVBAProject.VBComponents.Remove vbComp

                            On Error GoTo 0

                        End If

                End Select

            Next vbComp

        End If

    Next pVBAProject

    MsgBox "Sheet has been cleared of all modules"

End Sub

Open in new window

0
 

Author Closing Comment

by:NerishaB
Comment Utility
Thank you.  Works perfectly.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now