Excel - Save a copy of work book

I have tried a few different ways of doing this but I never get a good solution.
There has to be a simple way.

I have a Workbook. I would like create a copy on close.
This is the easy part..... I would love a prompt on close that says "would you like to create a copy now? - yes or no"

What has been the problem is, the copy version also tries to create a copy of itself. I don't want this.
The copy file should just go into a subfolder.  
master file      C:\Users\pike\Desktop and copy of file        C:\Users\pike\Desktop\backup\
Thanks experts.
chris pikeAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Hi Chris,

Please try the below code.
You may change the SaveAsPath as per your requirement but remember to have a back slash in the end of the path as shown in the code.
Sub SaveASFile()
Dim SaveAsPath As String
Dim Ans As String
Dim fileName As String
SaveAsPath = Environ("UserProfile") & "\Desktop\backup\"
fileName = Replace(ThisWorkbook.Name, ".xlsm", ".xlsx")
Ans = MsgBox("Do you want to Save A Copy?", vbQuestion + vbYesNo, "Confirm Please!")
If Ans = vbYes Then
   Application.DisplayAlerts = False
   ThisWorkbook.SaveAs SaveAsPath & fileName, 51
   Application.DisplayAlerts = True
End If
End Sub

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\pike\Desktop\backup\" & replace(activeworkbook.name,".xlsm",",".xlsx")  _
         , FileFormat:= xlOpenXMLWorkbook

Open in new window


by saving as an XLSX, all code will be removed

if you plan to do this more than once, it would be a good idea to add a date/time stamp to the filename -- or check if that filename is already there first and over-write it
0
 
yo_beeDirector of Information TechnologyCommented:
are you required to keep the same name of the file.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....
Place the code given below in ThisWorkbook Module...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim SaveAsPath As String
Dim Ans As String
Dim fileName As String
SaveAsPath = Environ("UserProfile") & "\Desktop\backup\"
fileName = Replace(ThisWorkbook.Name, ".xlsm", ".xlsx")
Ans = MsgBox("Do you want to Save A Copy?", vbQuestion + vbYesNo, "Confirm Please!")
If Ans = vbYes Then
   Application.DisplayAlerts = False
   ThisWorkbook.SaveAs SaveAsPath & fileName, 51
   Application.DisplayAlerts = True
End If
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
There is a built in function for creating a backup of a file when you save it with no need for additional VBA.

In the file you wish to keep a backup of choose the Save As option and then next to the Save button in the Save window use the Tools dropdown to select General Options. This is where you would set a Open or Modify password but there is also an option to "Always Create Backup". Tick this option and every time the file is saved a backup will be created, overwriting the previous backup.

Thanks
Rob H
0
 
chris pikeAuthor Commented:
Hi Rob@  I tried that but it creates a .blx file or something and it errors out on opening.
Not the most desirable solution, but thanks. I am still working on the other solutions.
Thanks Rob
0
 
chris pikeAuthor Commented:
Hi Neeraj
I think I am having trouble with this line.....
fileName = Replace(ThisWorkbook.Name, ".xlsm", ".xlsx")

when i run it like this it errors out on this line......
ThisWorkbook.SaveAs SaveAsPath & fileName, 51

Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Insert a new module and place the following code into that module and run the code and let me know what do you get in two msgbox. One will let you know ThisWorkbook's Name and other will let you know the NewFileName.

Sub fileName()
Dim fileName As String
MsgBox "ThisWorkbook's Name: " & ThisWorkbook.Name
fileName = Replace(ThisWorkbook.Name, ".xlsm", ".xlsx")
MsgBox "NewFileName: " & fileName
End Sub

Open in new window

0
 
chris pikeAuthor Commented:
Hmmm, Neeraj,
     It seems to work, but I don't need the 2 prompts for file name,and new filename.
It  just needs to copy itself..... I get why we saved as xlsx instead of xlsm, so the copy doesn't make backups too.

Hmmmmm....Can we modify the first code you wrote to just be a macro then I can hide a button on one sheet somewhere to push and it will make a copy on the spot of the whole working WB? (xlsm)
Thanks so much

oh and the SaveAsPath =       can i just put in a relative path like c:/ blah blah bla   ??? Like

SaveAsPath= "c:\user\desktop\backup"  like that.....Just because I will need to change the location of the actual backup folder.
0
 
chris pikeAuthor Commented:
Trying it out, Thanks.
Talk soon.
Appreciate it.
Chris
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Chris,

with all due respect, I brought up a point -- but didn't specifically ask it as a question (sorry, my mistake) since it WAS maybe a question that needed a response .   Others jumped in and you probably missed it.

"if you plan to do this more than once, it would be a good idea to add a date/time stamp to the filename -- or check if that filename is already there first and over-write it"

Also ... I did not give you an entire routine because I hoped you are learning more yourself now.  We are here to help -- but best to understand whatever you put into your projects because ultimately, the burden of support is on your shoulders.
0
 
chris pikeAuthor Commented:
Hi Crystal......
Long Time.....
I see your point with "more than once" but I think for my needs on this one, it is ok to just overwrite the file on every save.

I am better than I was for sure, but working two full time jobs and a little one at home, I have very little time to spend taking any courses.  At work I have so much to do and only need little fixes here and there. No time at work to study & learn unfortunately.  I can't remember what it is like to have a hobby anymore, or have free time to play games or "relax" even LOL...  That being said, that is why I pay for this subscription to EE to help write the codes I need to drop in. I don't have time for much else. Just need to drop in and maybe fiddle a bit with it.

I respect your point on helping me and hoping I will be able to learn more, but I just don't have enough hours in the day. I am not happy about that, and believe me, I would love to learn, but just not in the cards. Thanks so much Crystal
Kind Regards
Chris
0
 
chris pikeAuthor Commented:
@Neeraj
We are very very close.... I just need help with these two lines to make them work properly. I tried to drop them into different lines, but couldn't get them working.  ( see " >>>>>> " below)
Thanks so much.
Chris
I also changed the .XLSM to .XLS
 
Sub SaveASFile()
Dim SaveAsPath As String
Dim Ans As String
Dim fileName As String

>>>>>>>  ActiveWorkbook.SaveAs fileName:="C:\Users\pike\Desktop\backup\"

fileName = Replace(ThisWorkbook.Name, ".xlsm", ".xls")
Ans = MsgBox("Do you want to Save A Copy?", vbQuestion + vbYesNo, "Confirm Please!")
If Ans = vbYes Then
   Application.DisplayAlerts = False
   
 >>>>>>>>>    Workbook.Settings.CheckComptiliblity = False
   
   ThisWorkbook.SaveAs SaveAsPath & fileName, 51
   Application.DisplayAlerts = True
End If
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.