Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
can you have excel automatically save in 2 locations
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Default Save To directory doesn't work Excel 2003 | Excel Discussion (Misc queries) | |||
when I save a worksheet made in excel 2003 to excel 97, | Excel Discussion (Misc queries) | |||
How to save sheets in new excel files, automatically? | Excel Worksheet Functions | |||
create a macro to save excel sheet | Excel Worksheet Functions | |||
Excel 2003: File->Close without saving changes (save menu does no. | Excel Discussion (Misc queries) |