can you have excel automatically save in 2 locations Saving a Backup every time 29.09.2006 13:41:01 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?

RE: can you have excel automatically save in 2 locations tim m 29.09.2006 14:09:02 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. "Saving a Backup every time" wrote: [Quoted Text] > 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?

Re: can you have excel automatically save in 2 locations Dave Peterson <petersod[ at ]verizonXSPAM.net> 29.09.2006 15:23:24 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 Saving a Backup every time wrote: [Quoted Text] > > 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? -- Dave Peterson

Re: can you have excel automatically save in 2 locations Rookie 1st class 29.09.2006 16:22:01 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? "Dave Peterson" wrote: [Quoted Text] > 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 > > Saving a Backup every time wrote: > > > > 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? > > -- > > Dave Peterson >

Re: can you have excel automatically save in 2 locations Rookie 1st class 29.09.2006 16:36:02 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? "Rookie 1st class" wrote: [Quoted Text] > 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? > > "Dave Peterson" wrote: > > > 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 > > > > Saving a Backup every time wrote: > > > > > > 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? > > > > -- > > > > Dave Peterson > >

Re: can you have excel automatically save in 2 locations Dave Peterson <petersod[ at ]verizonXSPAM.net> 29.09.2006 19:45:06 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 Rookie 1st class wrote: [Quoted Text] > > 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? > "Rookie 1st class" wrote: > > > 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? > > > > "Dave Peterson" wrote: > > > > > 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 > > > > > > Saving a Backup every time wrote: > > > > > > > > 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? > > > > > > -- > > > > > > Dave Peterson > > > -- Dave Peterson

Re: can you have excel automatically save in 2 locations Rookie 1st class 30.09.2006 16:49:01 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. "Dave Peterson" wrote: [Quoted Text] > 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 > > > > Rookie 1st class wrote: > > > > 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? > > "Rookie 1st class" wrote: > > > > > 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? > > > > > > "Dave Peterson" wrote: > > > > > > > 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 > > > > > > > > Saving a Backup every time wrote: > > > > > > > > > > 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? > > > > > > > > -- > > > > > > > > Dave Peterson > > > > > > -- > > Dave Peterson >