NerishaB
asked on
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.R emove pVBAProject.VBComponents.I tem(i)
Any ideas?
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.R
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
ASKER
Thanks, I tried that. Can you help me with a function to remove all modules?
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 ("MyProjec t").VBComp onents.Rem ove vbComp
End If
Next vbCom
End If
Next prj
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
End If
Next vbCom
End If
Next prj
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
ASKER
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.
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.
I've used ThisWorkbook for testing purposes but you can use commented code refering to other workbook.
ASKER
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
Sheet modules can not be removed but the code inside of it will be cleared. Check it plese.
ASKER
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
modules.png
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Works perfectly.
pVBAProject.VBComponents.R
however you cannot remove Sheet modules that way as they are bound to the workbook sheets.