Delete macros after they run (excel xp, win2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Sheets.Copy

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

ThisWorkbook.Close SaveChanges:=False

Andrew C

Reply | Quote

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.

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

Reply | Quote

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.

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

Andrew

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks!! That explanation was very helpful as was the code you included. It’s always nice to understand WHY something works.

Troy

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote