.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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Why not just set the template as read only?

That way you can't save, only save as. No macro needed.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Why does it feel like that is something that I should have known :oops:
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top