I think that this may do what you require:
Sub macro3()
fileSaveName = Application.GetSaveAsFilename
If fileSaveName <> False Then
' look to see if filename has already been used
n1 = InStrRev(fileSaveName, "\")
fileShtNm = Right(fileSaveName, Len(fileSaveName) - n1)
filePath = Left(fileSaveName, n1 - 1)
Set fs = Application.FileSearch
With fs
.LookIn = filePath
.Filename = fileShtNm
If .Execute > 0 Then
MsgBox "This file has already been saved."
Else
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If
End With
End If
End Sub
"carl" wrote:
> I'm having problems with display alerts that I'm not sure is fixable. I have
> a macro that lets your choose a date from a form and then from that it takes
> data from one workbook and copies it in to three other workbooks. It then
> saves, closes and emails them to certain distribution lists. The problem is
> that the form allows the user to potentially pick a previous month that has
> already been saved and emailed. If it does then it comes up with an alert
> asking the user if they want to overwrite the workbook that already exists.
> But for me I would prefer to not even give the user an option. Is there a
> way of writing an If statement that says:
>
> If this alert comes up then end the sub and close the workbooks that were
> opened and created without saving Else continue running.
>
> Or just something that will basically also choose No when it ask if the user
> wants to over write the existing workbook.
>
> Otherwise I fear one day someone will choose Yes and they will send out the
> previous months workbooks again.
>
|
Similar Threads
