Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Macro (Excel 97)

    I have a spreadsheet open and I do the following to save the spreadsheet under a different name. How can I get it to delete any macros that I have or to make sure they are not executed.

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
    "tabsproddatabaseedgarsecuritytermination.xls ", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    Application.DisplayAlerts = True

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Delete Macro (Excel 97)

    Daniel

    I suggest that you copy the worksheets into a new workbook and then save that one. Cleaner than removing your VBA code and then by mistake saving it...

    Sheets(WhatEverSheets).Copy

    This will copy a worksheet into a new workbook and then your code will be the same. Just put this as the first line.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Macro (Excel 97)

    I tried that but had problems that were over my head. It kept stoping at => Windows("process.xls").Active

    Sub Send_right()
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
    "tabsproddatabaseedgarsecuritytermination.xls ", FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    Application.DisplayAlerts = True
    Windows("process.xls").Activate
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Terminations"
    Windows("Termination.XLS").Activate
    Sheets("Terminations").Copy After:=Workbooks("Termination.XLS").Sheets(3)
    Application.DisplayAlerts = False
    Sheets("Sheet1").Delete
    Sheets("Sheet2").Delete
    Sheets("Sheet3").Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs
    End Sub

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

    Re: Delete Macro (Excel 97)

    Daniel,

    If you want to copy just the worksheets of an existing workbook to a new file try :<pre>Sub CopyWB()
    Dim strNewFileName As String
    strNewFileName = "NewFileName" ' Assign suitable name for new file
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    ActiveWorkbook.SaveAs Filename:= strNewFileName
    End Sub</pre>

    The above assumes a workbook of three sheets named Sheet1, Sheet2 and Sheet3. Alter to suit your situation. If any of these sheets have code in the Worksheet Module it too will be copied. Code in a general module or Thisworkbook module should not transfer.

    Andrew C

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Macro (Excel 97)

    Just tried it and it worked. Now for the wrench..... The boss changed his mine now and only wants to delete one of the macros (Step1) and keep the others. Guess I have to start over on this one.

    This didn't go to waste as I can use it in another project we are working on.

  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 Macro (Excel 97)

    On the left pane in the VBE , and as part of the Project you will see Objects called Thisworkbook, Sheet1 etc. These conatin code modules apart from the general module. If you double click an any of them you should see the code pane for that object. But unless you deliberately put some code into them, don'r concern yourself with them. Module1 etc are general modules.

    Incidentally, the 3rd line of code I posted above could be amended to<pre>Sheets(Array(1, 2, 3)).Copy</pre>

    , which would mean the actual names of the sheets would not matter as the first 3 sheets would be copied. You can of course include 4, 5 etc. if required.

    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 Macro (Excel 97)

    Daniel,

    It is possible to delete individual macros with some code. The following macro will delete a macro in a module, where both the Module name and Macro (Procedure) name are passed to it. <pre> Sub RemoveMacro(Mod1 As String, Mac1 As String)
    Dim Line1 As Long
    Dim LineCount As Long
    With ThisWorkbook.VBProject.VBComponents(Mod1).CodeModu le
    Line1 = .ProcStartLine(Mac1, 0)
    LineCount = .ProcCountLines(Mac1, 0)
    .DeleteLines Line1, LineCount
    End With
    End Sub</pre>

    The above can then be called from anywhere in your code with <pre> Call RemoveMacro("Module1", "Macro1")</pre>

    Indeed you can have this macro delete itself.

    For more on coding the VBE, see <A target="_blank" HREF=http://www.cpearson.com/excel/vbe.htm> Pearson Software Consulting </A>

    Though it is usually required to set a reference to the Visual Basic for Applications Extensibility library when coding the VBE, the above code should work without doing so.

    Hope it is of assistance

    Andrew C

Posting Permissions

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