Handling save error messages using VBA

Question

I have the following macro to auto save a workbook:

ActiveWorkbook.SaveAs Filename:=Workbooks(2).Path & "\" & ActiveWorkbook.Name
ActiveWorkbook.Close savechanges:=True

but the problem I have if the destination folder already has file with this name I get a message from Excel saying that this file already exists and whether I want to replace it.

What I need is the following:

if user selects Yes, then replace the existing file
if user selects No (currently I get a runtime error), then it saves it with V2 at the end of the file name, if this exists then V3 and so on
if user selects Cancel, then they see a message saying Are you sure you want to cancel. If they confirm then it's cancelled, otherwise it returns to the error message.

Or perhaps the code can be edited so that when it saves as it checks if file already exists in folder and if it does, then save it as v2.

You need a separate function whose responsibility will be to come up with a file name. The Dir$ function will be useful to determine if a file exists, and UX-wise I would advise against having too many confirmation pop-ups, they get annoying for the users. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 24, 2016 at 15:05
As a side note, identifying a workbook by Workbooks(2) is dangerous. If they have other workbooks or addins open your code will save something other than what you expect. — Doug Glancy
– Doug Glancy, Commented Mar 24, 2016 at 15:14
Thanks Doug for your suggestion. There shouldn't be other books open as they will run macro from one book which will open different books, copy some data from some, vlookup from others and then copy and paste final table into last workbook which has to be saved to the same location as one of the opened workbooks. — Yuri
– Yuri, Commented Mar 24, 2016 at 16:08

SierraOscar · Accepted Answer · 2016-03-24 15:38:21Z

1

You would probably have to refactor your current code to use it 'as-is' - but I think this is the logic that you are looking for:

Dim saveName As Variant

retry:

saveName = Application.GetSaveAsFileName

If Not saveName = False Then
    If Len(Dir$(saveName)) = 0 Then
        ActiveWorkbook.SaveAs saveName
    Else
        MsgBox "Workbook already exists, please choose a different name.", vbOkOnly
        GoTo retry:
    End If
Else
    MsgBox "User cancelled save.", vbInformation"
End If

answered Mar 24, 2016 at 15:38

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathan_Sav · Accepted Answer · 2016-03-24 15:16:49Z

0

Something like

If Dir(strfilename) = "" Then
Else
    strfilename=Application.GetSaveAsFilename
End If

ActiveWorkbook.SaveAs strfilename, XlFileFormat.xlOpenXMLWorkbookMacroEnabled

answered Mar 24, 2016 at 15:16

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Handling save error messages using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related