.xlsm to .xlsx without reopening after "save as"?

golden_maze

New Member
Joined
Aug 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

I have a template file that I often use as a starting point for new projects. More often than not I start working on the file and habitually save my progress and therefore on accident overwrite the template. Very annoying.

As a solution, I want to write a macro that initiates "save as" and assign this macro the ctrl + s shortcut such that I can't save over my template on accident because Ctrl+s no longer saves but rather starts the "save as" macro. The newly created file should then no longer have this macro and I therefore want to save it as a .xlsx file.

I only have one problem: once the new file was saved it is indeed a .xlsx file but I need to close the Excel and reopen it so that the macro actually disappears. If after "saving as" I just keep working and press Ctrl+s to (regular) save, it still initiates the macro which shouldn't be in the file anymore because the file is .xlsx. I tried to get around it by some code I found which seems to "simulate" to reopen the file but it doesn't help.

The below is my code:

VBA Code:
Sub SaveAs()

Dim sFileSaveName As Variant
Dim myNewSheet As Worksheet
Set myNewSheet = ActiveSheet

sFileSaveName = Application.GetSaveAsFilename (Format (Now, "dd.mm.yyyy") & "_XXX_v01.xlsx", "Excel Files (*.xlsx), *.xlsx")
if sFileSaveName <> False Then
myNewSheet.Saveas fileName:=sFileSaveName, FileFormat :=51
End If

ThisWorkbook.Saved = True
ThisWorkbook.ChangeFileAccess xlReadOnly, , False
Application.Wait Now + TimeValue("00:00:01")
ThisWorkbook.ChangeFileAccess xlReadWrute, , True

End Sub

Help on this would be greatly appreciated! If there is another way to disable the macro once it has been used once for example then I would be open to that as well.

Thanks,
Marvin
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,994
Office Version
  1. 2019
Platform
  1. Windows
Why not just set the template as read only?

That way you can't save, only save as. No macro needed.
 

golden_maze

New Member
Joined
Aug 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thanks, that would definitely be the easiest solution.
However, it would be preferable if parts of the new title (e.g. today's date) get filed in as the file name automatically, I don't think that would work without a macro?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,994
Office Version
  1. 2019
Platform
  1. Windows
it would be preferable if parts of the new title (e.g. today's date) get filed in as the file name automatically
Fair point, if I'm honest I only read what you were trying to do but I didn't look at what your code was doing.

Give this a go as the first line of your code (change the name in double quotes to the name of your actual template).
VBA Code:
If ThisWorkbook.Name <> "Template.xlsm" Then Exit Sub
I did have to do something similar a while back, it's a file that I no longer use but I think I still have a copy of it, if the suggestion above doesn't work then I'll have a look for the other one.
 

golden_maze

New Member
Joined
Aug 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Oh, that's a neat idea. Worked great! I added that if the name doesn't match Ctrl+s saves the file like usual (because otherwise the regular Crl+s shortcut is still overwritten by the macro) and now it works perfectly.

Thank you so much for the prompt help! Have a good weekend.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,994
Office Version
  1. 2019
Platform
  1. Windows
You're welcome :)

For future reference, there is an event procedure that is triggered when you try to save, you don't need to re-map ctrl s for it to work.

Your code will not run if you use any other method of saving (e.g. file menu save icon) while the event code will detect all methods of saving.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,804

ADVERTISEMENT

If you are often using the same template, you can use the Save As Template... feature to save it as a template, either as .xltx or .xltm. When that is opened it will convert to either .xlsx or .xlsm and Save will not overwrite it.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,994
Office Version
  1. 2019
Platform
  1. Windows
Why does it feel like that is something that I should have known :oops:
 

golden_maze

New Member
Joined
Aug 29, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Good points, I'll look into the event procedure! In my case, the current way is actually not bad since that way I can still save changes in my starting template by using other methods of savings which then don't trigger the macro. But I am sure event procedures will come in handy in other situations.

Also didn't know about the excel template feature. I had a look and it seems indeed a very handy feature but as far as I could tell the automatic naming (e.g. adding today's date to the title) doesn't seem to be supported yet so for my specific use case a macro might be the better solution.

Appreciate all the feedback :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,523
Members
410,689
Latest member
ConfuzzledThomas
Top