Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a workbook in 2 places
I have a workbook that I constantly update with web queries throughout the
day. This is on my pc at work on my C:\ drive. I also keep a copy on a public network drive so co-workers can see the latest & greatest updates. What I want to do is whenever I save the book it will save to both places. Since I rarely close this book throughout the week, I just do saves after each update, I stayed away from the Before_Close event, and went with the Before_Save event. My code will save to the network drive, but crashes Excel at the end of the procedure, and therefore does not save to the local drive. I thought perhaps because I was saving the file with the same name in 2 places, it crashed because of duplicate file names. So I added "Copy of" to the name of the network file, but still no cookie. It still crashes. Can anybody point me in the right direction so when I click the Save (Disk) icon, it will save to both drives? I put the CurDir check in the code so that the network copy would not also run the code in case someone tried to save that file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim CrDr As String CrDr = CurDir If CurDir = "C:\My Documents" Then ChDir "\\Lafbdc1\dept\NCP" Application.EnableEvents = False ActiveWorkbook.SaveAs "\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" ChDir CrDr End If Application.EnableEvents = True End Sub Mike F |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a workbook in 2 places
I think if I were doing this, I'd stay away from the events and just provide a
macro that would save the workbook twice: option explicit sub SaveMeTwice() with thisworkbook .save .savecopyas filename:="\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" end with end sub Maybe put a button on the worksheet and assign the macro to that button. ps. Your code blew up for me, too. The chdir won't work with UNC paths--so you don't need it. And after the workbook saves as the new name, workbook that is open would be that "Copy of..." workbook. I'm not sure if that's what you really wanted anyway. pps. After I deleted the chdir stuff, the code still crashed. (I used xl2003 in my testing (winXP home).) Mike Fogleman wrote: I have a workbook that I constantly update with web queries throughout the day. This is on my pc at work on my C:\ drive. I also keep a copy on a public network drive so co-workers can see the latest & greatest updates. What I want to do is whenever I save the book it will save to both places. Since I rarely close this book throughout the week, I just do saves after each update, I stayed away from the Before_Close event, and went with the Before_Save event. My code will save to the network drive, but crashes Excel at the end of the procedure, and therefore does not save to the local drive. I thought perhaps because I was saving the file with the same name in 2 places, it crashed because of duplicate file names. So I added "Copy of" to the name of the network file, but still no cookie. It still crashes. Can anybody point me in the right direction so when I click the Save (Disk) icon, it will save to both drives? I put the CurDir check in the code so that the network copy would not also run the code in case someone tried to save that file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim CrDr As String CrDr = CurDir If CurDir = "C:\My Documents" Then ChDir "\\Lafbdc1\dept\NCP" Application.EnableEvents = False ActiveWorkbook.SaveAs "\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" ChDir CrDr End If Application.EnableEvents = True End Sub Mike F -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a workbook in 2 places
Thanks for the effort, Dave. I'm still not sure why it blows up. The code
started with a simple 'SaveAs' and I kept adding stuff to it trying to figure out the key. I may end up taking your advice. I have a custom menu I could add a Save item to. Hey! Better yet, I could put it on my Personal.xls and use it to save my other workbooks that have copies on the network. And the code wouldn't be in either copy. There, I'm glad we had this talk. Mike F "Dave Peterson" wrote in message ... I think if I were doing this, I'd stay away from the events and just provide a macro that would save the workbook twice: option explicit sub SaveMeTwice() with thisworkbook .save .savecopyas filename:="\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" end with end sub Maybe put a button on the worksheet and assign the macro to that button. ps. Your code blew up for me, too. The chdir won't work with UNC paths--so you don't need it. And after the workbook saves as the new name, workbook that is open would be that "Copy of..." workbook. I'm not sure if that's what you really wanted anyway. pps. After I deleted the chdir stuff, the code still crashed. (I used xl2003 in my testing (winXP home).) Mike Fogleman wrote: I have a workbook that I constantly update with web queries throughout the day. This is on my pc at work on my C:\ drive. I also keep a copy on a public network drive so co-workers can see the latest & greatest updates. What I want to do is whenever I save the book it will save to both places. Since I rarely close this book throughout the week, I just do saves after each update, I stayed away from the Before_Close event, and went with the Before_Save event. My code will save to the network drive, but crashes Excel at the end of the procedure, and therefore does not save to the local drive. I thought perhaps because I was saving the file with the same name in 2 places, it crashed because of duplicate file names. So I added "Copy of" to the name of the network file, but still no cookie. It still crashes. Can anybody point me in the right direction so when I click the Save (Disk) icon, it will save to both drives? I put the CurDir check in the code so that the network copy would not also run the code in case someone tried to save that file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim CrDr As String CrDr = CurDir If CurDir = "C:\My Documents" Then ChDir "\\Lafbdc1\dept\NCP" Application.EnableEvents = False ActiveWorkbook.SaveAs "\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" ChDir CrDr End If Application.EnableEvents = True End Sub Mike F -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a workbook in 2 places
Sometimes just saying things out loud will get you to a solution.
Mike Fogleman wrote: Thanks for the effort, Dave. I'm still not sure why it blows up. The code started with a simple 'SaveAs' and I kept adding stuff to it trying to figure out the key. I may end up taking your advice. I have a custom menu I could add a Save item to. Hey! Better yet, I could put it on my Personal.xls and use it to save my other workbooks that have copies on the network. And the code wouldn't be in either copy. There, I'm glad we had this talk. Mike F "Dave Peterson" wrote in message ... I think if I were doing this, I'd stay away from the events and just provide a macro that would save the workbook twice: option explicit sub SaveMeTwice() with thisworkbook .save .savecopyas filename:="\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" end with end sub Maybe put a button on the worksheet and assign the macro to that button. ps. Your code blew up for me, too. The chdir won't work with UNC paths--so you don't need it. And after the workbook saves as the new name, workbook that is open would be that "Copy of..." workbook. I'm not sure if that's what you really wanted anyway. pps. After I deleted the chdir stuff, the code still crashed. (I used xl2003 in my testing (winXP home).) Mike Fogleman wrote: I have a workbook that I constantly update with web queries throughout the day. This is on my pc at work on my C:\ drive. I also keep a copy on a public network drive so co-workers can see the latest & greatest updates. What I want to do is whenever I save the book it will save to both places. Since I rarely close this book throughout the week, I just do saves after each update, I stayed away from the Before_Close event, and went with the Before_Save event. My code will save to the network drive, but crashes Excel at the end of the procedure, and therefore does not save to the local drive. I thought perhaps because I was saving the file with the same name in 2 places, it crashed because of duplicate file names. So I added "Copy of" to the name of the network file, but still no cookie. It still crashes. Can anybody point me in the right direction so when I click the Save (Disk) icon, it will save to both drives? I put the CurDir check in the code so that the network copy would not also run the code in case someone tried to save that file. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim CrDr As String CrDr = CurDir If CurDir = "C:\My Documents" Then ChDir "\\Lafbdc1\dept\NCP" Application.EnableEvents = False ActiveWorkbook.SaveAs "\\Lafbdc1\dept\NCP\Copy of NCP Work.xls" ChDir CrDr End If Application.EnableEvents = True End Sub Mike F -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving a workbook in two places. | Excel Discussion (Misc queries) | |||
Shared workbook - to save or not to save | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Macro needed; save a file two (or more places) at the same time | Excel Programming | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |