Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

VBA Excel - Remove all modules from Workbook after export

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
NerishaB
Asked:
NerishaB
  • 6
  • 5
1 Solution
 
MSmaxImplementation ConsultantCommented:
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
 
NerishaBAuthor Commented:
Thanks, I tried that.  Can you help me with a function to remove all modules?
0
 
WiBCommented:
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
MSmaxImplementation ConsultantCommented:
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
 
NerishaBAuthor Commented:
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
 
MSmaxImplementation ConsultantCommented:
I've used ThisWorkbook for testing purposes but you can use commented code refering to other workbook.
0
 
NerishaBAuthor Commented:
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
 
MSmaxImplementation ConsultantCommented:
Sheet modules can not be removed but the code inside of it will be cleared. Check it plese.
0
 
NerishaBAuthor Commented:
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
 
NerishaBAuthor Commented:
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
 
MSmaxImplementation ConsultantCommented:
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
 
NerishaBAuthor Commented:
Thank you.  Works perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now