Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Delete macros after they run (excel xp, win2000)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

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

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete macros after they run (excel xp, win2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete macros after they run (excel xp, win2000)

    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>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete macros after they run (excel xp, win2000)

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

  11. #11
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

  13. #13
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete macros after they run (excel xp, win2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •