Solved

VBA Excel - Remove all modules from Workbook after export

Posted on 2010-08-31
12
684 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
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

Suggested Solutions

Title # Comments Views Activity
Web Query 1 19
Text box keydown event does not work 38 39
Is there a way to link Access 2003 app to an xlsx sheet? 6 22
Excel Macro 9 18
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 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