can you have excel automatically save in 2 locations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Saving a Backup every time - 29 Sep 2006 14:41 GMT

I need to make a backup onto a server for every file I save. Is there a way
I can have excel save 2 copies - one where I have access, and one in the
backup file in my server?

Reply to this Message

tim m - 29 Sep 2006 15:09 GMT

In the past when I have had to do this I create a 'SAVE' button at the top of
the sheet. i then record a simple macro that saves the file to the local
computer and then saves the file to the server. I then attach the macro to
the save button. Then when I want to save I click the Save button on the
spreadsheet and it saves in both places.

> I need to make a backup onto a server for every file I save. Is there a way
> I can have excel save 2 copies - one where I have access, and one in the
> backup file in my server?

Reply to this Message

Dave Peterson - 29 Sep 2006 16:23 GMT

You could use a macro:

option explicit
sub SaveTwice()
with activeworkbook
.save
.savecopyas "C:\mybackups\" & .name
end with
end sub

Change the folder location to what you need.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

> I need to make a backup onto a server for every file I save. Is there a way
> I can have excel save 2 copies - one where I have access, and one in the
> backup file in my server?

Signature

Dave Peterson

Reply to this Message

Rookie 1st class - 29 Sep 2006 17:22 GMT

Dave I did something similar on close, It adds the date to the file name and
saves it in "//netusers*/Current Year". My problem is it leaves the program
in the directory "*.Current Year" on exit. How do I remain in the current
directory on exit?

> You could use a macro:
>
[quoted text clipped - 14 lines]
> > I can have excel save 2 copies - one where I have access, and one in the
> > backup file in my server?

Reply to this Message

Rookie 1st class - 29 Sep 2006 17:36 GMT

Current Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'On Close
Range("BckUp").Select
Application.ScreenUpdating = False
ActiveSheet.Unprotect
response = MsgBox(Prompt:="Would You Like to Create a Back-Up Copy?",
Buttons:=vbYesNoCancel + vbDefaultButton1, Title:="BACK-UP COPY")
If response = vbYes Then
BookName = ActiveWorkbook.Name
Selection = ClearContents
Selection = InputBox(Prompt:="Your Back-Up Form Name Will Be Like
the Window Below.", Title:="BACK-UP COPY", Default:=Range("BckUpDt").Text + "
" + (BookName))
If ActiveWorkbook.Saved = False Then ActiveWorkbook.SaveAs
On Error GoTo Oops
ChDir "\\Server1\Netusers\Current Year"
ActiveWorkbook.SaveCopyAs Filename:=Range("BckUp").Text
ActiveWorkbook.Saved = True
ChDir "\\Server1\Netusers\Lab"
Oops:
ChDir "\Current Year"
ActiveWorkbook.SaveCopyAs Filename:=Range("BckUp").Text
ActiveWorkbook.Saved = True
ChDir "\Pyro\Lab"
ElseIf response = vbCancel Then
Exit Sub
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

There are sub directories under "Lab". I Know I generate the change
directory. How can I do the same thing without the CD?

> Dave I did something similar on close, It adds the date to the file name and
> saves it in "//netusers*/Current Year". My problem is it leaves the program
[quoted text clipped - 19 lines]
> > > I can have excel save 2 copies - one where I have access, and one in the
> > > backup file in my server?

Reply to this Message

Dave Peterson - 29 Sep 2006 20:45 GMT

First, I don't think that this line actually works:

ChDir "\\Server1\Netusers\Current Year"

I've never seen ChDir work on a UNC path. But the good thing is you don't need
to change drives/folders to save the file. You can just specify the path in the
.savecopyas line

ActiveWorkbook.SaveCopyAs _
Filename:=\\Server1\Netusers\Current Year\" & Range("BckUp").Text

> Current Code:
>
[quoted text clipped - 59 lines]
> > >
> > > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rookie 1st class - 30 Sep 2006 17:49 GMT

Final Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'On Close
Range("BckUp").Select
Application.ScreenUpdating = False
ActiveSheet.Unprotect
response = MsgBox(Prompt:="Would You Like to Create a Back-Up Copy?",
Buttons:=vbYesNoCancel + vbDefaultButton1, Title:="BACK-UP COPY")
If response = vbYes Then
BookName = ActiveWorkbook.Name
Selection = ClearContents
Selection = InputBox(Prompt:="Your Back-Up Form Name Will Be Like
the_ Window Below.", Title:="BACK-UP COPY", Default:=Range("BckUpDt").Text +
" " +_ (BookName))
On Error GoTo Oops
ActiveWorkbook.SaveCopyAs Filename:="\\Server1\Netusers\Current
Year"_ & Range("BckUp").Text
Oops:
ActiveWorkbook.SaveCopyAs Filename:="\Pyro\Lab" & Range("BckUp").Text
ElseIf response = vbCancel Then
Exit Sub
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

For the originator: 01 Jan I rename "Current Year" to 200? and create a new
"Current Year". That way I don't have to rewrite my macros every year. I take
a date block "mm/dd/yy" and save a copy "mm-dd-yy" that is added to the name
of the workbook in a format that can be written to a filename. This example
works on a networked or stand alone computer. Delete the OOPS if your system
isn't networked.

For Dave; I owe you a carbonated beverage of your choice. Thank You.
Lou
PS. the original formula was written in Excel 97 and did change directories.

> First, I don't think that this line actually works:
>
[quoted text clipped - 70 lines]
> > > >
> > > > Dave Peterson

Reply to this Message