can you have excel automatically save in 2 locations

  • Thread starter Saving a Backup every time
  • Start date
S

Saving a Backup every time

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?
 
T

tim m

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.
 
R

Rookie 1st class

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?
 
R

Rookie 1st class

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?
 
D

Dave Peterson

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
 
R

Rookie 1st class

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.
 
Top