Save As behaviour

[Peter]

Hope someone can help, as is usually the case I'm probably missing
something really obvious!

I have a button on a form that I want to save the last contents of the
current workbook to a specific name as follows...

Private Sub cbEnd_Click()

Dim fname As String

fname = "Last end status"
ActiveWorkbook.SaveAs Filename:=fname,
ConflictResolution:=xlLocalSessionChanges
Unload Me

End Sub

I have another subroutine, part of which does exactly the same thing but
at specific periods as data changes...

fname = "Status at" + Str(vaNextClock) + " secs"

vaCurrClock = vaCurrClock Mod 5
vaNextClock = vaNextClock Mod 5

If vaCurrClock <> vaNextClock And vaNextClock = 0 Then
ActiveWorkbook.SaveAs Filename:=fname,
ConflictResolution:=xlLocalSessionChanges
End If

My issue is that the second piece of code works fine but the first piece
always shows the confirm dialog box, does anyone have any idea why? Or
even better how to force the overwite?

Thanks in advance

Peter

 

[Tom Ogilvy]

Private Sub cbEnd_Click()

Dim fname As String

Application.DisplayAlerts = False
fname = "Last end status"
ActiveWorkbook.SaveAs Filename:=fname,
ConflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True
Unload Me

End Sub

or
Private Sub cbEnd_Click()

Dim fname As String

fname = "Last end status"
On Error Resume Next
Kill fName & ".xls"
On Error goto 0
ActiveWorkbook.SaveAs Filename:=fname,
ConflictResolution:=xlLocalSessionChanges
Unload Me

End Sub

 

[Peter]

Private Sub cbEnd_Click()

Dim fname As String

Application.DisplayAlerts = False
fname = "Last end status"
ActiveWorkbook.SaveAs Filename:=fname,
ConflictResolution:=xlLocalSessionChanges
Application.DisplayAlerts = True
Unload Me

End Sub

Thanks Tom, that's a bit more elegant than my workaround using KILL. Any idea why the second worked with no issues or is it an "undocumented feature"?

Peter

 

[Tom Ogilvy]

Probably because you don't have any existing workbooks that have exactly
that name. If you eventually have such duplicate named books, you will
probably have the problem.

--
Regards,
Tom Ogilvy

Thanks Tom, that's a bit more elegant than my workaround using KILL. Any

idea why the second worked with no issues or is it an "undocumented
feature"?