Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
My macro asks the user for a FileSaveAsName, then tries to save the file with:
ActiveWorkbook.SaveAs Filename:=sFileSaveName, ... How can I tell if the user gets the "File exists, Overwrite?" prompt, and answers "no"? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
Hi
You let the user do user things and check if the file path and filename is identical to sFileSaveName in the end. But I suspect this is not your final goal. You want to always overwrite, or not allow overwrite, or not to ask the user ? HTH. Best wishes Harald "OceansideDJ" skrev i melding ... My macro asks the user for a FileSaveAsName, then tries to save the file with: ActiveWorkbook.SaveAs Filename:=sFileSaveName, ... How can I tell if the user gets the "File exists, Overwrite?" prompt, and answers "no"? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
Harald,
If the user picks "No" to the overwrite, I want to ask for another filename. Currently, if the the user picks "no", I get runtime error 1004, Method "SaveAs of object '_Workbook' failed. Could I detect error 1004? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
Hi OceansideDJ,
OceansideDJ wrote: If the user picks "No" to the overwrite, I want to ask for another filename. Currently, if the the user picks "no", I get runtime error 1004, Method "SaveAs of object '_Workbook' failed. Could I detect error 1004? Yes, you can use On Error Goto or On Error Resume to trap runtime error 1004. Here's one way: Sub Demo() Dim vFileName As Variant Dim bSaved As Boolean vFileName = True Do Until (vFileName = False) Or bSaved vFileName = Application.GetSaveAsFilename On Error Resume Next If vFileName < False Then ActiveWorkbook.SaveAs vFileName bSaved = (Err.Number = 0) End If On Error GoTo 0 Loop If bSaved Then MsgBox "Saved" End Sub -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
Jake,
Excellent. Thanks for a more elegant solution than mine. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If user says "No" to FileSaveAs Overwrite?
Another way is to check for yourself:
Option Explicit Sub testme01() Dim myFileName As Variant Dim OkToSave As Boolean Dim resp As Long Do myFileName = Application.GetSaveAsFilename _ (filefilter:="Excel files, *.xls") If myFileName = False Then Exit Sub End If OkToSave = True If Dir(myFileName) = "" Then 'do nothing special Else resp = MsgBox(prompt:="Overwrite Existing file?", _ Buttons:=vbYesNoCancel) Select Case resp Case Is = vbCancel MsgBox "Try again later" Exit Sub Case Is = vbNo OkToSave = False End Select End If If OkToSave Then Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=myFileName, _ FileFormat:=xlWorkbookNormal Application.DisplayAlerts = True Exit Do End If Loop End Sub OceansideDJ wrote: Harald, If the user picks "No" to the overwrite, I want to ask for another filename. Currently, if the the user picks "no", I get runtime error 1004, Method "SaveAs of object '_Workbook' failed. Could I detect error 1004? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 displays Locked by "another user", does not show User I | Excel Discussion (Misc queries) | |||
How do I suppress the "Do you want to overwrite the destination cells" message | Setting up and Configuration of Excel | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do you turn off "Save a copy/Overwrite changes" dialog box | Excel Discussion (Misc queries) | |||
Find a workbook and "overwrite it with the latest Excel format" | Excel Programming |