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