Mirror a workbook

[Guest]

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,

 

[Guest]

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.

 

[Guest]

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

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.

 

[Dave Peterson]

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 here:
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.

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

 

[Guest]

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]

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

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

 

[Guest]

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

 

[Peter T]

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

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

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

 

[Dave Peterson]

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

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

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