Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Dear experts,
I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
i have a macro that does pretty much the same. save to my
c drive, saves to network drive and saves to a flash drive for off system backup. I used the Application.DisplayAlerts=false and reset to true after the save.(recommended) I put mine in a standard module and run in from an icon. try the standart module instead of the before save event. from what you described, this is just a straight save and not one that needs to be triggered by an event. good luck. -----Original Message----- Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi,
unfortunately this is not a straight save, because many users can make changes to this workbook on the server, and every time one of them makes a change, it needs to be reflected to the mirror workbook... Can somebody please help me with this? Many thanks, best regards, Valeria " wrote: i have a macro that does pretty much the same. save to my c drive, saves to network drive and saves to a flash drive for off system backup. I used the Application.DisplayAlerts=false and reset to true after the save.(recommended) I put mine in a standard module and run in from an icon. try the standart module instead of the before save event. from what you described, this is just a straight save and not one that needs to be triggered by an event. good luck. -----Original Message----- Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Does your macro blow up or does excel crash?
If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi Dave,
it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi Valeria
Maybe you can adapt something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sPath sPath = "C:\temp\" If ThisWorkbook.Path & "\" = sPath Then Exit Sub Me.SaveCopyAs "C:\temp\" & Me.Name End Sub Regards, Peter "Valeria" wrote in message ... Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Your code didn't cause excel (xl2002 under win98) to crash for me.
But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi,
Peter's code works fine for me (THANKS!!!!)! Instead, the one I was using before, even with the .enableevents line, does not work. Something very strange (at least to me!) is happening with this latter code: when I save a workbook for the first time (I tried with an empty test workbook, to test if there was a prblem with mine, as Dave suggested), the workbook is saved, but the code is only kept in the copy of the workbook, and eliminated from the original one! And if I try to write it again in the original workbook, Excel crashes (I work with Excel 2002 under XP). By the way, the error I get is AppName: excel.exe AppVer: 10.0.6713.0 ModName: mso.dll ModVer: 10.0.5004.0 Offset: 00063651 If anybody kows why, I am very curious... Thanks! Best regards, Valeria "Dave Peterson" wrote: Your code didn't cause excel (xl2002 under win98) to crash for me. But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
I think the reason the original code wasn't kept in the original workbook is you
never saved it in the original workbook. Each time you saved your workbook, you saved it to that other workbook. One way around it is to disable events, then save to anywhere you want, then reenable events so your code can run next time. I like this better than my original suggestion: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False Me.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True Cancel = True End Sub Notice that I replaced Activeworkbook with Me. Inside the ThisWorkbook module, Me and ThisWorkbook are equivalent. And I added a "cancel = true" line. This stops excel from trying to do the "real" save after your code finishes. (I'm still not sure why it would cause excel to crash, though. My gut feeling is that the workbook is getting corrupted--but that's just a guess.) Valeria wrote: Hi, Peter's code works fine for me (THANKS!!!!)! Instead, the one I was using before, even with the .enableevents line, does not work. Something very strange (at least to me!) is happening with this latter code: when I save a workbook for the first time (I tried with an empty test workbook, to test if there was a prblem with mine, as Dave suggested), the workbook is saved, but the code is only kept in the copy of the workbook, and eliminated from the original one! And if I try to write it again in the original workbook, Excel crashes (I work with Excel 2002 under XP). By the way, the error I get is AppName: excel.exe AppVer: 10.0.6713.0 ModName: mso.dll ModVer: 10.0.5004.0 Offset: 00063651 If anybody kows why, I am very curious... Thanks! Best regards, Valeria "Dave Peterson" wrote: Your code didn't cause excel (xl2002 under win98) to crash for me. But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi Dave,
with the cancel=true line Excel does not crash, but as you say, it does not save the original version of the spreadsheet, either. And if I comment out that line, Excel crashes... The only thing that seems to work is Peter's code Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sPath sPath = "C:\temp\" If ThisWorkbook.Path & "\" = sPath Then Exit Sub Me.SaveCopyAs "C:\temp\" & Me.Name End Sub Again I do not know why, my Excel is very sensitive, it might be that some other application on my computer gets in conflict with it... Thanks! Best regards, Valeria "Dave Peterson" wrote: I think the reason the original code wasn't kept in the original workbook is you never saved it in the original workbook. Each time you saved your workbook, you saved it to that other workbook. One way around it is to disable events, then save to anywhere you want, then reenable events so your code can run next time. I like this better than my original suggestion: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False Me.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True Cancel = True End Sub Notice that I replaced Activeworkbook with Me. Inside the ThisWorkbook module, Me and ThisWorkbook are equivalent. And I added a "cancel = true" line. This stops excel from trying to do the "real" save after your code finishes. (I'm still not sure why it would cause excel to crash, though. My gut feeling is that the workbook is getting corrupted--but that's just a guess.) Valeria wrote: Hi, Peter's code works fine for me (THANKS!!!!)! Instead, the one I was using before, even with the .enableevents line, does not work. Something very strange (at least to me!) is happening with this latter code: when I save a workbook for the first time (I tried with an empty test workbook, to test if there was a prblem with mine, as Dave suggested), the workbook is saved, but the code is only kept in the copy of the workbook, and eliminated from the original one! And if I try to write it again in the original workbook, Excel crashes (I work with Excel 2002 under XP). By the way, the error I get is AppName: excel.exe AppVer: 10.0.6713.0 ModName: mso.dll ModVer: 10.0.5004.0 Offset: 00063651 If anybody kows why, I am very curious... Thanks! Best regards, Valeria "Dave Peterson" wrote: Your code didn't cause excel (xl2002 under win98) to crash for me. But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
Hi Valarie,
Glad to here that worked. It's probably the easiest way to save a backup, normally of course with a different name, extension and/or folder. Hence the Exit Sub bit if trying to save the file that was opened in the "other" folder. On reflection might be a bit simpler like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) Dim str As String str = "C:\Temp\" str = str & Me.Name If str = Me.FullName Then Exit Sub Me.SaveCopyAs str End Sub I havn't looked at the problems you and Dave have been discussing, except I'm reminded of something Dave once said, from memory - Me: Doctor, Doctor - it hurts when I do that Doctor: Don't do that Regards, Peter "Valeria" wrote in message ... Hi Dave, with the cancel=true line Excel does not crash, but as you say, it does not save the original version of the spreadsheet, either. And if I comment out that line, Excel crashes... The only thing that seems to work is Peter's code Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sPath sPath = "C:\temp\" If ThisWorkbook.Path & "\" = sPath Then Exit Sub Me.SaveCopyAs "C:\temp\" & Me.Name End Sub Again I do not know why, my Excel is very sensitive, it might be that some other application on my computer gets in conflict with it... Thanks! Best regards, Valeria "Dave Peterson" wrote: I think the reason the original code wasn't kept in the original workbook is you never saved it in the original workbook. Each time you saved your workbook, you saved it to that other workbook. One way around it is to disable events, then save to anywhere you want, then reenable events so your code can run next time. I like this better than my original suggestion: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False Me.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True Cancel = True End Sub Notice that I replaced Activeworkbook with Me. Inside the ThisWorkbook module, Me and ThisWorkbook are equivalent. And I added a "cancel = true" line. This stops excel from trying to do the "real" save after your code finishes. (I'm still not sure why it would cause excel to crash, though. My gut feeling is that the workbook is getting corrupted--but that's just a guess.) Valeria wrote: Hi, Peter's code works fine for me (THANKS!!!!)! Instead, the one I was using before, even with the .enableevents line, does not work. Something very strange (at least to me!) is happening with this latter code: when I save a workbook for the first time (I tried with an empty test workbook, to test if there was a prblem with mine, as Dave suggested), the workbook is saved, but the code is only kept in the copy of the workbook, and eliminated from the original one! And if I try to write it again in the original workbook, Excel crashes (I work with Excel 2002 under XP). By the way, the error I get is AppName: excel.exe AppVer: 10.0.6713.0 ModName: mso.dll ModVer: 10.0.5004.0 Offset: 00063651 If anybody kows why, I am very curious... Thanks! Best regards, Valeria "Dave Peterson" wrote: Your code didn't cause excel (xl2002 under win98) to crash for me. But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mirror a workbook
The thing that would scare me is that I don't see anything that would cause your
code to crash excel (or even just fail to run). I've seen lots more posts about workbooks being sensitive than excel being sensitive. (I'd still worry about the workbook being corrupted--but I said that already.) Good luck and keep your fingers crossed <vbg. Valeria wrote: Hi Dave, with the cancel=true line Excel does not crash, but as you say, it does not save the original version of the spreadsheet, either. And if I comment out that line, Excel crashes... The only thing that seems to work is Peter's code Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sPath sPath = "C:\temp\" If ThisWorkbook.Path & "\" = sPath Then Exit Sub Me.SaveCopyAs "C:\temp\" & Me.Name End Sub Again I do not know why, my Excel is very sensitive, it might be that some other application on my computer gets in conflict with it... Thanks! Best regards, Valeria "Dave Peterson" wrote: I think the reason the original code wasn't kept in the original workbook is you never saved it in the original workbook. Each time you saved your workbook, you saved it to that other workbook. One way around it is to disable events, then save to anywhere you want, then reenable events so your code can run next time. I like this better than my original suggestion: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False Me.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True Cancel = True End Sub Notice that I replaced Activeworkbook with Me. Inside the ThisWorkbook module, Me and ThisWorkbook are equivalent. And I added a "cancel = true" line. This stops excel from trying to do the "real" save after your code finishes. (I'm still not sure why it would cause excel to crash, though. My gut feeling is that the workbook is getting corrupted--but that's just a guess.) Valeria wrote: Hi, Peter's code works fine for me (THANKS!!!!)! Instead, the one I was using before, even with the .enableevents line, does not work. Something very strange (at least to me!) is happening with this latter code: when I save a workbook for the first time (I tried with an empty test workbook, to test if there was a prblem with mine, as Dave suggested), the workbook is saved, but the code is only kept in the copy of the workbook, and eliminated from the original one! And if I try to write it again in the original workbook, Excel crashes (I work with Excel 2002 under XP). By the way, the error I get is AppName: excel.exe AppVer: 10.0.6713.0 ModName: mso.dll ModVer: 10.0.5004.0 Offset: 00063651 If anybody kows why, I am very curious... Thanks! Best regards, Valeria "Dave Peterson" wrote: Your code didn't cause excel (xl2002 under win98) to crash for me. But I would add a couple of .enableevents lines to prevent the code from calling itself: Option Explicit Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False Application.EnableEvents = False ActiveWorkbook.SaveAs _ Filename:="C:\windows\temp\test.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.EnableEvents = True Application.DisplayAlerts = True End Sub When bad things happen that seem to make no sense, sometimes (not always) running Rob Bovey's code cleaner can magically fix things: Rob Bovey's codecleaner can be found he http://www.appspro.com/ ======== If you comment out this code and save the workbook, does it cause excel to crash? If yes, maybe the workbook is corrupted and needs to rebuilt. (an awful prospect, I know, but it might be the only solution.) If you build a small test workbook and add this code, does excel crash? If no, then maybe you'll have lots to recreate very soon! Here's hoping you find the trouble. Valeria wrote: Hi Dave, it is Excel that crashes. My code is Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ "C:\my_path\Targets SH_PP.xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Application.DisplayAlerts = True End Sub Is it because this code is also copied on the freshly saved workbook, and Excel begins to loop in saving the workbook? If that's the case, how can I avoid it? Many thanks! Best regards, Valeria "Dave Peterson" wrote: Does your macro blow up or does excel crash? If it's the macro, you may want to post your _beforesave code. Valeria wrote: Dear experts, I would like to mirror a copy of a certain workbook on another server. Basically, every time my source workbook is saved, it needs to be saved both on its server, and on another one, deleting the previous copy of the workbook present there. I have tried with the "workbook_beforesave" event, but my Excel crashes down when trying to execute the macro. I have put all an Application.DisplayAlerts=false also there, but it does not seem to work... Could you please help me? Many thanks in advance, best regards, -- Valeria -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mirror View | Excel Discussion (Misc queries) | |||
Mirror Wildcard? | Excel Worksheet Functions | |||
Mirror spreadsheet? | Excel Discussion (Misc queries) | |||
Mirror Image | Excel Worksheet Functions | |||
2 mirror files with :1 and :2 - why? | Excel Discussion (Misc queries) |