Link to home
Start Free TrialLog in
Avatar of chris pike
chris pikeFlag for Canada

asked on

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.
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

    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
are you required to keep the same name of the file.
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

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
Avatar of chris pike

ASKER

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
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
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

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.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Trying it out, Thanks.
Talk soon.
Appreciate it.
Chris
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.
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
@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