Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Macros (Excel 2000)

    Is there a way to Save As a spreadsheet that contains a macro, without having the macro saved into the new spreadsheet? Or removed the macro code before saving?



    Thanks
    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Macros (Excel 2000)

    Activate the Visual Basic Editor (Alt+F11)
    Right-click each module in the Project Explorer.
    Select Remove <module> and specify that you don't want to export it before removing it.
    Switch back to Excel.
    Use File | Save As to save the workbook under another name.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    Sorry, I was not detailed enough with my question. I can do it manually, but I am looking for a way to do it from code.

    The spread sheet receives data from Access. The macros run to format the data. Then using code in Access I am saving the sheet under a different name than the one I opened. Can I remove the code from the Excel sheet using Access to automate the removal?


    Thanks
    Richard

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

    Re: Remove Macros (Excel 2000)

    Is it one worksheet in the workbook, or many worksheets? The easiest way is to copy the worksheet to a new workbook, and then save that. Do you need help doing that
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    USA
    Posts
    386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    In the final workbook, only one sheet will be required. I think I can manage the copy.


    Thanks to all for your help.
    Richard

  6. #6
    New Lounger
    Join Date
    Jan 2003
    Location
    Ontario, Canada
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    Hi:
    I am jumping into this thread because I have tried the method you suggest, but when I look for "Modules" in Project Explorer I don't have any; I only have the "Microsoft Excel Objects". Obviously, this makes it difficult to "Remove module". <img src=/S/confused.gif border=0 alt=confused width=15 height=20> In addition, I have tried to copy the sheets to a new book, in the hopes that the macros would not survive the copy process, but it seems they do: after saving that workbook, and re-opening it, it again tells me that macros exist, and asks me if I want to disable or enable them.Is it possible that I am not actually dealing with a macro, but with some other type of object that "appears" to be a macro, but is not?

    Eric Halang

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Macros (Excel 2000)

    Hi Eric,

    Have you checked whether any of the items listed under Microsoft Excel Objects contains code? You cannot remove these modules, but you can clear them.

    If the workbook is "old", it's also possible that it contains a hidden Excel 4.0 macro sheet or Excel 5.0 dialog sheet.

    One of the worksheets might contain an object that causes the warning. You can try Edit | Go To..., click Special, select Objects and click OK to find out which objects a sheet contains.

  8. #8
    New Lounger
    Join Date
    Jan 2003
    Location
    Ontario, Canada
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    Hi, Hans;
    I tried the "Edit, Go To, Special, Objects", but no objects were found. On the other hand, all but one of the sheets DO contain the following, when I double-click them in the Project screen (or click View Code):
    *****
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    End Sub
    *****
    This appeared to be causing the false macro warning, because I simply highlighted and deleted them, and after saving and reopening, there was no macro warning! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    Thanks for the hints: do you forsee any problems with deleting those entries?

    Eric

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove Macros (Excel 2000)

    1) they were not really "false macro warning", since you do have a macro in the sheet

    2) The macros are doing nothing so it is safe to remove them

    Steve

  10. #10
    New Lounger
    Join Date
    Jan 2003
    Location
    Ontario, Canada
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    Hi, Steve;

    I guess I can understand that they might actually be considered macros, but then why do they not show under any of the macro tools that are provided to remove them. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    I have removed them, and the user tells me all is well.

    Thanks;
    Eric

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

    Re: Remove Macros (Excel 2000)

    They do not show in the macro tools because they are event routines not regular macros and can not be run as a normal macro from the macro tools dialog box. The macro tools dialob box only shows macros that can be run from the box. For example, Function procedures do not show up there either.
    Legare Coleman

  12. #12
    New Lounger
    Join Date
    Jan 2003
    Location
    Ontario, Canada
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Macros (Excel 2000)

    Hi, Legare;

    Thanks for the explanation. Still seems a little "wrong" to me (not your explanation, but the way Excel works): If they are going to call them macros (when opening the file), then the Macro tool should allow you to delete them. But maybe that's just me. :-)

    Eric

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Macros (Excel 2000)

    The terminology is not entirely accurate: it should mention "VBA code" instead of "macros". All macros contain VBA code, but not all VBA code consists of macros. But on the other hand, many users have at least heard of macros, while they might not know what is meant by VBA. So Microsoft chose to use the more familiar, if less accurate term.

Posting Permissions

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