Solved

VBA Excel - Remove all modules from Workbook after export

Posted on 2010-08-31
12
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 7

Expert Comment

by:MSmax
ID: 33566591
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
ID: 33566669
Thanks, I tried that.  Can you help me with a function to remove all modules?
0
 
LVL 3

Expert Comment

by:WiB
ID: 33566828
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Expert Comment

by:MSmax
ID: 33566854
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
ID: 33567141
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
ID: 33567319
I've used ThisWorkbook for testing purposes but you can use commented code refering to other workbook.
0
 

Author Comment

by:NerishaB
ID: 33567381
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
ID: 33567423
Sheet modules can not be removed but the code inside of it will be cleared. Check it plese.
0
 

Author Comment

by:NerishaB
ID: 33567561
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
ID: 33567902
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
ID: 33568111
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
ID: 33568173
Thank you.  Works perfectly.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 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