Solved

Excel - Save a copy of work book

Posted on 2016-10-13
13
79 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 18
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 21

Expert Comment

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

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

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

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 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 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 18
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now