Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs and File Exists
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs and File Exists
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 -- Regards, Tom Ogilvy "Office User" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused here Prevent Saving File - but allow ONLY File SAVEAS Met | Excel Discussion (Misc queries) | |||
How check for No/Cancel Button when SaveAs and file already exists? | Excel Programming | |||
Saveas where file already exists | Excel Programming | |||
SaveAs command when a file already exists? | Excel Programming | |||
the file already exists - do you want to replace the existing file? | Excel Programming |