File Saving Script Problems

[mike79]

I have this script that auto saves a document:

Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
Sheets("Sheet1").Range("a1") = " " & Date & " klokken " & Time
ActiveWorkbook.SaveAs "C:\Temp\Dreports" & Format(Date, "mmddyy") & ".xls"
End Sub


It does what I want, but crash's everytime it saves.
Is it something I put in that is wrong?

My file is a 4 sheet document.

 

[Legacy 98055]

I think you have entered loopyville. Every time your code calls the SaveAs method, you are causing the Workbook_BeforeSave event to fire again and again and again and again... Disable events before you SaveAs and then re-enable events afterwards...

<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_BeforeSave(ByVal SaveAsUi <font color="#0000A0">As</font> Boolean, Cancel <font color="#0000A0">As</font> Boolean)
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">GoTo</font> Err_Workbook_BeforeSave
       Application.EnableEvents = <font color="#0000A0">False</font>
       Sheets("Sheet1").Range("a1") = " " & <font color="#0000A0">Date</font> & " klokken " & Time
       ActiveWorkbook.SaveAs "C:\Temp\Dreports" & Format(Date, "mmddyy") & ".xls"
  Err_Workbook_BeforeSave:
       Application.EnableEvents = <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("92520061621489").value=document.all("92520061621489").value.replace(/<br \/>\s\s/g,"");document.all("92520061621489").value=document.all("92520061621489").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("92520061621489").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="92520061621489" wrap="virtual">
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
On Error GoTo Err_Workbook_BeforeSave
Application.EnableEvents = False
Sheets("Sheet1").Range("a1") = " " & Date & " klokken " & Time
ActiveWorkbook.SaveAs "C:\Temp\Dreports" & Format(Date, "mmddyy") & ".xls"
Err_Workbook_BeforeSave:
Application.EnableEvents = True
End Sub</textarea>

 

[mike79]

thanks!!
That worked great..
Didn't realize I had that loop going.