Office User
05-15-2005, 08:03 AM
Trying to determine if file exists when saving but it doesn't seem to be working. In my testing, the same file names doesn't exist in my Invoice folder but it always comes up with my FileSave3 message so it must be thinking it is.
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"
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
xld posted some suggestions for file exist in response to a question on 5/7/2005. However, that was using code in ThisWorkbook for Open. My open code is a macro in Modules instead so not sure if that would work the same. The template came that way so I'm not brave enough to move the open code to ThisWorkbook for fear I'll "break" it.
Thanks for any input,
Marcia
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"
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
xld posted some suggestions for file exist in response to a question on 5/7/2005. However, that was using code in ThisWorkbook for Open. My open code is a macro in Modules instead so not sure if that would work the same. The template came that way so I'm not brave enough to move the open code to ThisWorkbook for fear I'll "break" it.
Thanks for any input,
Marcia