Solved

Excel - Save a copy of work book

Posted on 2016-10-13
13
91 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 20
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 22

Expert Comment

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

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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 33

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 30

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 30

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

696 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