Save backup copy in different location

[sachavez]

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.

 

[phxsportz]

Here's a backup routine that I use that pretty much does exactly what you're asking for.. modify to your hearts content...

    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]

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]

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

 

[sachavez]

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]

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]

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

 

[sachavez]

Adam,

You might want to read this thread: http://www.mrexcel.com/forum/showthread.php?t=401005&highlight=autosave


Steve