Save backup copy in different location

sachavez

Active Member
Joined
May 22, 2009
Messages
446
I wondered if someone could help me with some code that would allow me to save a copy of my working file in a different location.

My primary file is saved in c:\work\Excel Files\

I would like to have a backup file saved c:\backup\Excel Files\

Additionally, it would be really cool if the backup file would be saved with the "save date" at the end of the file, i.e. abc_8-21-09.xls

Thank you, in advance.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
Here's a backup routine that I use that pretty much does exactly what you're asking for.. modify to your hearts content...

Code:
    With Worksheets("SETUP")
        'This will create a backup copy of the worksheet and name it as follows
        'backup path from setup D8 + 2 char prefix id from setup D5 + _BACKUP_ + date +Time
        'Example would be c:\backup\SP_BACKUP_10-43_AM_05-30-2009.xls
        Prefix = .Cells(4, "D")
        fname = .Cells(8, "D") & Prefix & "_Liquor_BACKUP_" & (Replace(Date, "/", "-")) & "_" & _
                (Replace(Left(Time, (InStr(1, Time, ":") + 2)), ":", "-") & Right(Time, 2)) & "_ (v) " & .Cells(4, "G") & ".xls"
    End With
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs Filename:=fname
    Application.DisplayAlerts = True
    Worksheets("backup").Activate
    With Sheets("backup")
        t_row = (Last_Row("backup!A") + 1)
        .Cells(t_row, 1) = Prefix
        .Cells(t_row, 2) = Date
        .Cells(t_row, 3) = Time
        .Cells(t_row, 4).Select
        .Hyperlinks.Add Anchor:=Selection, Address:=fname    'Hyper link the Backup File
        Result = MsgBox("Your data was successfully backed up to file" & Chr(10) & Chr(13) & Chr(10) & Chr(13) & fname, _
                        vbOKOnly, "TA DAHHHH")
    End With
    Worksheets("Main").Select
End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Try:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa()<br>    <br>    ThisWorkbook.SaveCopyAs Filename:= _<br>        "C:\backup\Excel Files\" & _<br>        Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , vbTextCompare) - 1) & _<br>        "_" & Format(Date, "yyyy-mm-dd") & ".xls"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    </FONT>

Hope that helps,

Mark
 

phxsportz

Well-known Member
Joined
Jun 11, 2006
Messages
1,985
I should have probably explained all the extra's that mine does so as not to confuse..

I have two bars in Phoenix and I have a liquor control workbook to manage my stock in both bars independantly. Each bar has it's own code "SP" and "FS". When I back a file up I preceed it with that code, the date, and the time. I also maintain a sheet called backup that logs every backup made, be it manual or automatic so I can easily restore..

GTO's Code is far simpler and concise.. I'd go with it if I were you..

;)


PS.. HOLEY CRAP We Got Rain in the DESERT :biggrin:
 

sachavez

Active Member
Joined
May 22, 2009
Messages
446
PhxSportz - Thanks for the code. I'm going to follow your advise and work with GTO's code.


GTO - Your code worked perfectly! However, is there a way I can have the macro save the backup copy each time I click on the save button? Is Private Sub an option?

Thank you!

Steve
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings Steve,

'Private Sub' has to do with whether he procedure can be accessed from outside the module.

To answer your question, you could insert the code in the BeforeSave event of the ThisWorkbook module. Respectfully/humbly however, as far as I can see, this simply saves two files that are "good" or two files that are "goobered".

Mark
 

adamsash

New Member
Joined
Nov 30, 2006
Messages
30
Hi Guys,

just wondering if there is an easy way to get this to do an auto save and overwrite the file every 10 mins or so?

My IT infrastructure at work is terrible, and I have lost days of work a few times... It would be great to have the confidence knowing you have a 10min old version saved safely in a backup file on your local drive.

Thanks,

Adam
 

Watch MrExcel Video

Forum statistics

Threads
1,095,325
Messages
5,443,817
Members
405,251
Latest member
shanezer

This Week's Hot Topics

Top