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.

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



John V wrote:

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