SaveAs and File Exists

[Office User]

Using code to save to particular folder with particular name. It works but
always displays my message about where the file is saved, even when saving
updates of the same invoice in the same "session" (in other words the
template was not closed and reopened). I guess this more of an annoyance than
a problem but I'd like it to work like Save and SaveAs in "normal" Excel
files. That is - if it's the first time to save the file, it uses SaveAs.
After that, it already knows the file exists so just Saves using the same
file name.

Here's the code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Const FileSave1 = "Your invoice has been saved in the Invoices folder on c:\ "
Const FileSave2 = "Please note the file name in the title bar above which
includes the customer name (if entered) and today's date"

Dim sPath As String
sPath = "C:\Invoices\"

'disables Excel's normal Save and SaveAs prompts
Application.DisplayAlerts = False
Application.EnableEvents = False

'sets check value so date and invoice number are not updated when re-opened
Range("check").Value = "x"

ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Now(),
" mm.dd.yyyy") & ".xls"
MsgBox FileSave1 & FileSave2, vbOKOnly + vbInformation, "File Saved"
Cancel = True

'turn on Excel alerts and normal events
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

I have toyed with trying to use FileExist but it's not worked with my
attempts shows below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const FileSave1 = "Your invoice has been saved in the Invoices folder on
c:\. "
Const FileSave2 = "Please note the file name in the title bar above which
includes the customer name (if entered) and today's date"
Const FileSave3 = "A file with this name and date already exists. Are you
sure you want to replace it?"

Dim sPath As String
Dim FileExists As Boolean
Dim Response
sPath = "C:\Invoices\"
FileExists = (Len(Dir(sPath))) > 0

'disables Excel's normal Save and SaveAs prompts
Application.DisplayAlerts = False
Application.EnableEvents = False

'sets check value so date and invoice number are not updated when re-opened
Range("check").Value = "x"

If FileExists = True Then
Response = MsgBox(FileSave3, vbYesNo, "File Exists")
If Response = vbNo Then
Cancel = True
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Now(),
" mm.dd.yyyy") & ".xls"
Cancel = True
End If

Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Now(),
" mm.dd.yyyy") & ".xls"
MsgBox FileSave1 & FileSave2, vbOKOnly + vbInformation, "File Saved"
Cancel = True
End If

'turn on Excel alerts and normal events
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

The user could leave cell "data5" (which is a customer name) blank and it
will save the file with just the date. It's very likely the user will have
more than 1 invoice in a day. If the user forgets to enter customer name
twice (if not more) in the same day, they'd lose the first invoice without
knowing it. I'd like to eventually add a check for If Range("data5").Value =
"" Then prompt them to enter name before continuing. However, I'd like to
take it 1 step at a time and make sure the File Exist piece works correctly
as well as the Save and SaveAs I was just mentioning.

Thanks for any input,
Marcia

 

[Tom Ogilvy]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Const FileSave1 = "Your invoice has been saved in the Invoices folder on
c:\. "
Const FileSave2 = "Please note the file name in the title bar above which
includes the customer name (if entered) and today's date"
Const FileSave3 = "A file with this name and date already exists. Are you
sure you want to replace it?"

Dim sPath As String
Dim FileExists As Boolean
Dim Response
sPath = "C:\Invoices\"
FileExists = (Len(Dir(sPath & _
ThisWorkbook.name)) > 0)

'disables Excel's normal Save and SaveAs prompts
Application.DisplayAlerts = False
Application.EnableEvents = False

'sets check value so date and invoice number are not updated when re-opened
Range("check").Value = "x"

If FileExists = True Then
Response = MsgBox(FileSave3, vbYesNo, "File Exists")
If Response = vbNo Then
Else
ActiveWorkbook.Save
End If

Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Now(),
" mm.dd.yyyy") & ".xls"
MsgBox FileSave1 & FileSave2, vbOKOnly + vbInformation, "File Saved"
End If
Cancel = True
'turn on Excel alerts and normal events
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub