SaveAs Issues

[Guest]

Here is my macro, pulled from elsewhere in this discussion group:

Sub saveascsv()

Dim SaveName
SaveName = ThisWorkbook.Path & "\CSV Output.csv"

Worksheets("Summary").Activate
Worksheets.Copy
Workbooks(Workbooks.Count).Activate
Workbooks(Workbooks.Count).SaveAs SaveName, xlCSV,
ConflictResolution:=xlLocalSessionChanges
Workbooks(Workbooks.Count).Close SaveChanges:=False

End Sub

My questions:
1. How do I "trap" the error whenever a prior version of "CSV Output" is
open? I'm thinking of a user msg such as "Please close CSV Ouput and try
again" with a Retry button.
2. How do I avoid the user being asked if s/he wants to overwrite an
existing version of the file? In this particular application, it is never
wrong to do so. The above code doesn't work.

Many thanks.

 

[Dave Peterson]

The easy question first:

#2. You can use application.displayalerts = false, do the save, and then turn
it back to True. This will avoid the "wanna overwrite" prompt.

#1. I'd try the save and report the error if there is one.

Combined:

Option Explicit
Sub saveascsv()

Dim SaveName As String
SaveName = ThisWorkbook.Path & "\CSV Output.csv"

Worksheets("Summary").Copy 'to a new workbook

With ActiveWorkbook
Application.DisplayAlerts = False
On Error Resume Next
.SaveAs Filename:=SaveName, FileFormat:=xlCSV
If Err.Number <> 0 Then
MsgBox "File Not Saved" & vbLf & _
Err.Number & "--" & Err.Description
Err.Clear
End If
On Error GoTo 0
Application.DisplayAlerts = True
.Close savechanges:=False
End With

End Sub