PDA

View Full Version : Sleeper: File Exists issue



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

Office User
05-15-2005, 08:16 AM
On a related note to the File Exists. Below is code I was using before attempting to determine if the file existed and it worked. The problem was it would always display my message about where the file was saved. Which really isn't a big 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



The whole reason I started thinking I'd better check if file exists, is because the user could leave cell "data5" (which is a customer name) blank. 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.

Sorry if I'm getting too confusing. Perhaps, it's not possible what I'm trying to do and I'll have to just live with it one way or the other.

Thanks for any input,
Marcia

Bob Phillips
05-15-2005, 08:26 AM
Hi Marcia,

Looking at your code, it seems that you tets for the directory, but without the directory flag, not the file.

Amended code is 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 sName As String
Dim FileExists As Boolean
Dim Response
sPath = "C:\Invoices\"
sName = Range("data5").Value & Format(Now(), " mm.dd.yyyy") & ".xls"
FileExists = (Len(Dir(sPath & sName))) > 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 & sName
Cancel = True
End If
Else
ActiveWorkbook.SaveAs Filename:=sPath & sName
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