Saving a spreadsheet without saving the Macro

[Guest]

I have a spreadsheet that I use as a template. When a user opens the
spreadsheet it runs a macro (using Auto_Open). This macro does its thing
with the data and then saves the spreadsheet.

The problem is that the macro is saved with the spreadsheet. Because of the
Auto_Open, the user has to dispable macros when they open the saved
spreadsheet.

Is there a way, using VBA, to save the spreadsheet without the macro?

Thank you.

 

[Ron de Bruin]

Is it a real template ? xlt

 

[Guest]

Sorry, no it's just a spreadsheet. I guess I shouldn't call it that. Would
it make a difference if I saved it as a template?

Thanks

 

[Ron de Bruin]

If you save it as a real template you can test the path

You can test the ThisWorkbook path
A real template have no path

Sub Auto_Open()
If Len(ThisWorkbook.Path) = 0 Then
MsgBox "run the code it is a template"
Else
MsgBox "The file is a xls do nothing"
End If
End Sub

 

[Ron de Bruin]

Another way is to delete the VBA
See Chip Pearson's site
http://www.cpearson.com/excel/vbe.htm

 

[Guest]

Thanks I will check that out.

Maybe I am going about this the wrong way. When the user opens my
spreadsheet it opens a text file, does lots of formating and totalling, and
then saves the spreadsheet in a folder with a different name.

I thought that there might be something I could add to the code that saves
the spreadsheet so that it no longer has the macro.

I tried saving it as a template but it still runs the macro. The user can
always disable macros but I would rather they didn't have to do that.

Thanks

 

[Ron de Bruin]

Working OK for me in the template

The macro will run but now you can do what you want

Sub Auto_Open()
If Len(ThisWorkbook.Path) = 0 Then
MsgBox "run the code it is a template"
Else
MsgBox "The file is a xls do nothing"
End If
End Sub

when you open the template you will see the first msgbox and when you save it as a xls and open the file you see the
second one(you can delete this line)

This way the code will only run if the file is a xlt and not ais it is xls

 

[Guest]

I tried that out and it works!

Thanks for your help!