Remove Macros (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

Activate the Visual Basic Editor (Alt+F11)
Right-click each module in the Project Explorer.
Select Remove 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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Thanks to all for your help.

Reply | Quote

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”. 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

Reply | Quote

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.

Reply | Quote

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!

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

Eric

Reply | Quote

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

Reply | Quote

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.

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

Thanks;
Eric

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote