This will return a 1004 runtime error and stop if the file already exists and the and the user selects No or Cancel during the "save as".
'Save file Dim Path As String Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" ActiveWorkbook.SaveAs Path & "filename.xlsm" MsgBox "File filename.xlsm saved to desktop!"
One way around this error.
Dim Path As String Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" If Dir(Path & "filename.xlsm") <> vbNullString Then MsgBox "file already exists": Exit Sub ActiveWorkbook.SaveAs Path & "filename.xlsm" MsgBox "File filename.xlsm saved to desktop!"
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
It has to complete a save or pass the No/Cancel or nasty things happen. So while that works, it effectively stops if the file exists.Originally Posted by bakerman2
One way around this error.
Dim Path As String Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" If Dir(Path & "filename.xlsm") <> vbNullString Then MsgBox "file already exists": Exit Sub ActiveWorkbook.SaveAs Path & "filename.xlsm" MsgBox "File filename.xlsm saved to desktop!"
If you want to save the workbook anyway then
Dim Path As String Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" Application.DisplayAlerts = False ActiveWorkbook.SaveAs Path & "filename.xlsm" Application.DisplayAlerts = True MsgBox "File filename.xlsm saved to desktop!"
When selecting Yes on msgbox old file will be deleted and new file is saved under the same name.
Otherwise msgbox file wasn't saved.
Dim Path As String Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" If Dir(Path & "filename.xlsm") <> vbNullString Then If MsgBox("file already exists", vbYesNoCancel) = vbYes Then Kill Path & "filename.xlsm" ActiveWorkbook.SaveAs Path & "filename.xlsm" MsgBox "File filename.xlsm saved to desktop!" Else MsgBox "File isn't saved" End If End If End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks