Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - Save a copy of work book

Posted on 2016-10-13
13
Medium Priority
?
121 Views
Last Modified: 2016-10-27
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.
0
Comment
Question by:chris pike
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 23
ID: 41842788
    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
 
LVL 24

Expert Comment

by:yo_bee
ID: 41842792
are you required to keep the same name of the file.
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41843006
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 34

Expert Comment

by:Rob Henson
ID: 41843238
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
 

Author Comment

by:chris pike
ID: 41843919
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
 

Author Comment

by:chris pike
ID: 41843937
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
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41843969
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
 

Author Comment

by:chris pike
ID: 41844062
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
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 total points
ID: 41844636
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
 

Author Comment

by:chris pike
ID: 41854584
Trying it out, Thanks.
Talk soon.
Appreciate it.
Chris
0
 
LVL 23
ID: 41854608
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
 

Author Comment

by:chris pike
ID: 41857128
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
 

Author Comment

by:chris pike
ID: 41857143
@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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question