can you delete a macro when a file is copied but preserve it in the original?

Question

I have a workbook called Expenses Sheet.xls located in R:\LGS\FINANCES\ losts of people refer to it and often save a copy so they can manipulate the data. There is an autosave macro in the original file savingto a fixed location. Is it possible to delete all macros in the new (save As) copy so the new copy does not have the macros contained in the original expenses Sheet.xls? This would stop all and sundires copies being svaed in my backup file as is currently happening.

Answer 1

First of all, theres nothing in your Excel file that executes when the file is being copied. At most, you'd be able to hook into an event which disabled or removed the macros when people saved the document somewhere else, from inside Excel, using the "File->Save As..." menu item, but if they drag and drop the file using Windows Explorer, you're out of luck in that regard.

However, have you thought about simply making all those macros check if this is actually the document that is supposed to be saved in that place?

Like this (note, I'm altogether unfamiliar with Excel macro syntax at this point so this will probably be wrong, but you should get the idea):

IF NOT DocumentLocation = "R:\LGS\FINANCES\ThisSpreadsheet.XLS" Then
    Exit Sub
END

This way, the macros are still there, and will still execute, but they will essentially do nothing for everyone else.

Would that be an acceptable solution for your case?

Answer 2

Lasse V. Karlsen's suggestion is spot on, the exact syntax could be

If Not Left(ThisWorkbook.Path,16) = "R:\LGS\FINANCES\" Then
    Exit Sub
End If