Macro mapping/location macro changing

Posted by Daniel on November 26, 2001 5:27 AM

I have mapped a macro to a custom button, in the macro Im using a save as command.

ChDir ("C:\SSS-SpreadSheet") 'Changes directory
NewNameFileName = Manifest(1, 1) & ".xls"

ThisWorkbook.SaveAs FileName:=NewNameFileName

The only thing wrong with this is the macro is saving to the new file name instead of staying with the excel sheet I have made as a template (C:\Program Files\Microsoft Office\Templates\Spreadsheet Solutions\SSS.xlt).

I there a way to save a macro to a place and map that place to the custom button, so the macro is not re-associated with the new .xls?

Posted by Bib on November 26, 2001 6:08 AM

When you save as, you save the macro as well. Try to delete the macro before saving with :

ActiveWorkbook.VBProject.VBComponents.Remove( ActiveWorkbook.VBProject.VBComponents("MyModule") )

Don't know if it works but it could be an idea. Or maybe save your file as a csv or txt...

Hope it helps