Delete macros after they run (excel xp, win2000)

[jha900]

I have some vba procedures in a main.xls file. The main.xls adds new sheets to it and then takes these sheets and copies them to another workbook. The newworkbook.xls has the macro/vba code in it also. How do i delete it from these files OR how do i disable the macro message that comes up when the user opens the newworkbook.xls? thanks for the help

[LegareColeman]

Can you show us your code? It is a little difficult to figure out how to do this without seeing what you are doing.

[jha900]

Workbooks.Open Filename:=oldname & "data.xls"

'code that adds 10 worksheets
'code that does calculations and makes graphs on these worksheets

ActiveWorkbook.SaveAs Filename:=NewFile & "data.xls"
ActiveWorkbook.Close SaveChanges:=True

The problems is that the NewFiledata.xls has the macros in it from oldfiledata.xls. When the user opens the file they get a message if they want to enable the macros. I needs to make this message stop appearing. For now I have been deleting the macros after i make newfiledata.xls but would like to automate this part. thanks

[Kevin]

You can write a mod that deletes all the code including itself. Add the VBA Extensibility Library to the references list and explore the the VBIDE objects.
You'll have to experient a bit, but here's the general idea. Don't forget "on error" or call the code from a global or something. You might get an error after deleting the executing code. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

Dim vbComp As VBIDE.VBComponent
Dim vbComps As VBIDE.VBComponents
Sub test()
Set vbComps = Application.VBE.ActiveVBProject.VBComponents
Dim c As Integer
For Each vbComp In vbComps
c = vbComp.CodeModule.CountOfLines
While c > 0
vbComp.CodeModule.DeleteLines 1, c
Wend
Next
End Sub

[Gary Frieder]

Can you configure things so that the code is in an Excel add-in (.xla) rather than in the .xls itself? That way, when you create a new .xls based on the original .xls, the new one would have no code in it.

Gary

[Andrew Cronnolly]

Just copy all th eworksheets to a new workbook and save that with thye new file name. Unless you have code in the workbook object or in any of the sheet objects, then the code should not be part of th enew workbook. Include the following lines in red, in th eprocedure you have already posted.

Workbooks.Open Filename:=oldname & "data.xls"

'code that adds 10 worksheets
'code that does calculations and makes graphs on these worksheets

<font color=red>Sheets.Copy</font color=red>

ActiveWorkbook.SaveAs Filename:=NewFile & "data.xls"
ActiveWorkbook.Close SaveChanges:=True

<font color=red>ThisWorkbook.Close SaveChanges:=False</font color=red>

Andrew C

[Andrew Cronnolly]

Kevin,

A technicality in Excel (I do not know about Word) causes it to report the presence of Macros once a general or class module is present, even if empty. So removing the codelines would not inhibit the alert on opening the workbook. The following code should remove all modules, and any code in document codepanes such as worksheets and chartsheets. It also works without setting an explicit refernce to the Extensibility library. <pre>Dim VBC, VBComp
Set VBC = Application.VBE.ActiveVBProject.VBComponents
For Each VBComp In VBC
Select Case VBComp.Type
Case Is <> 100
VBC.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp</pre>

Andrew

[Kevin]

The only reason I mentioned the VBA Extensibility lib is so the coder can "play around" with the objects using intellisense. No production code should have that reference. IMHO.

[SammyB]

Here's some code that I used to remove all of the VBA code from all of the Excel and Powerpoint files that were open. You should get the idea from this. You want this code to reside in Personal.XLS so that it won't delete itself. I think that you'll need to add a reference to Microsoft Visual Basic for Applications Extensibility. HTH --Sam
<pre>Sub DeleteAllMacroCode()
' First Excel
Dim xlBook As Excel.Workbook
Dim rsp As Integer
Dim vbComp As VBIDE.VBComponent
For Each xlBook In Excel.Workbooks
If UCase(xlBook.Name) <> "PERSONAL.XLS" Then ' Skip personal macro
workbook
rsp = MsgBox("Delete code in " & xlBook.Name, vbYesNoCancel,
"Delete")
If rsp = vbCancel Then Exit Sub
If rsp = vbYes Then
For Each vbComp In xlBook.VBProject.VBComponents
If vbComp.Type = vbext_ct_Document Then
With vbComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
Else
xlBook.VBProject.VBComponents.Remove vbComp
End If
Next vbComp
End If
End If
Next xlBook

' Now PoPo
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
On Error Resume Next
Set ppApp = GetObject(, "PowerPoint.Application")
If Err.Number = 0 Then
On Error GoTo 0
For Each ppPres In ppApp.Presentations
rsp = MsgBox("Delete code in " & ppPres.Name, vbYesNoCancel,
"Delete")
If rsp = vbCancel Then Exit Sub
If rsp = vbYes Then
For Each vbComp In ppPres.VBProject.VBComponents
If vbComp.Type = vbext_ct_Document Then
With vbComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
Else
ppPres.VBProject.VBComponents.Remove vbComp
End If
Next vbComp
End If
Next ppPres
End If
On Error GoTo 0
End Sub</pre>

[jha900]

Thanks u soooo much! I can use this for a lot of things. I really appreciate it.

[TroyWells]

Andrew or anyone,

I found this codeworks great for me, but would like some understanding.

When I test this the first VBComp fits the Case Else statement. After this is complete, I don't see any code modules disappear, so what is it deleting?

Why does Case Is <> 100 find entire modules? What is <> 100?

By the way, I'm using this with Word 2000. My slightly altered version of the codes is:

Dim VBC, VBComp
Set VBC = ActiveDocument.VBProject.VBComponents
For Each VBComp In VBC
Select Case VBComp.Type
Case Is <> 100
VBC.Remove VBComp
Case Else
With VBComp.codemodule
.deletelines 1, .countoflines
End With
End Select
Next VBComp


Thanks for the help!!
Troy

[Andrew Cronnolly]

Troy,

VB Components are made up of different types such as general modules, class modules and userforms together with the actual documentt object itseld, in Word ThisDocument. As the document objects can contain code (event procedures such as New, Open and Close and possibly others). As we cannot delete these document objects using this type of code (thankfully), we need to remove the code lines within those objects, and that is what happens when Case Else is executed.

As it happens the various VBComponents have a type property, and conveniently the document type is 100 so we can test for that find other types such as general modules (Type 1) calss modules (type 2) and userforms (type 3). So where the type is not 100 we can remove the component, code and all otherwise just remove the codelines.

There are constants to represent the component types (e.g a Document module is vbext_ct_Document, which evaluates to 100) but to use them a reference to the VBA Extensibility library is required. But I took shortcuts and perhaps some will not approve.

Try the following in both Word and Excel :

Dim VBC, VBComp
Set VBC = Application.VBE.ActiveVBProject.VBComponents
For Each VBComp In VBC
Debug.Print VBComp.Name & vbTab & VBComp.Type
Next VBComp

When using it include userforms, general modules etc in the project.

Hope this helps,

Andrew

[TroyWells]

Thanks!! That explanation was very helpful as was the code you included. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> It's always nice to understand WHY something works.

Troy