How to handle error when user press NO or Cancel

Question

I am working on macro to export some data to new workbook. Everything works as it should.

But if the exported file already exists in directory, program asks if you want to replace it with a new one.

If you press Yes, everything works perfect but if you press NO or CANCEL program will throw an error:

Run-time error '1004': Method SaveAs of object'_Workbook' failed

I think I should to something with this line:

wkb.SaveAs Filename:=relativePath

but I have no idea how to do it.

Sub ExportData()

    'create and save new workbook
    Dim wkb As Workbook
    Set wkb = Workbooks.Add
    relativePath = ThisWorkbook.Path & "\" & "WorkbookName.xlsx"
    wkb.SaveAs Filename:=relativePath
End Sub

Is all of the code necessary? Show the code that is giving you a problem - the rest is irrelevant — urdearboy, Oct 09 '18 at 22:06
wkb.SaveAs Filename:=relativePath This line is throwing error — Martin Lauf, Oct 09 '18 at 22:08
You've posted far too much code. Isolate the problem to a specific area, and then post only the code necessary to reproduce it. See [mcve]. Also, *program will throw an error* is not a problem description. What *specific* error does it *throw*? What is the exact error message? It's on your screen, right in front of you. It should be included in your post so we have that information as well. — Ken White, Oct 09 '18 at 22:10
It would be better if we could see an [MCVE](https://stackoverflow.com/help/mcve) so we don't spend time parsing code that works. — cybernetic.nomad, Oct 09 '18 at 22:10
Stop replying in comments. Instead, [edit] your post, remove all of the code that is not necessary, and clearly state the problem and include the error message. Again, see [mcve]. — Ken White, Oct 09 '18 at 22:14
See [VBA check if file exists](https://stackoverflow.com/q/16351249/4088852). — Comintern, Oct 09 '18 at 22:17
My problem is not that the file exists, because if I press YES, file is replaced without error. But when user press No or CANCEL, program will stop work. — Martin Lauf, Oct 09 '18 at 22:19
Do you always want to save the file? Or will there be times to actually want to use "no"/"cancel"? — BruceWayne, Oct 09 '18 at 22:28
sometimes, want to avoid it. Because if user forgot to move(rename) previous file, he will want to keep it and not replace. — Martin Lauf, Oct 09 '18 at 22:33

Martin Lauf · Answer 1 · 2018-10-09T23:00:32.977

0

I was finally able to solve the problem this way.

Dim wkb As Workbook
Set wkb = Workbooks.Add
relativePath = ThisWorkbook.Path & "\" & "WorkbookName.xlsx"

If Not Dir(relativePath, vbDirectory) = vbNullString Then
If MsgBox("File already exists, do you want to continue and replace it?", vbYesNo, "Wanna replace existing file?") = vbNo Then
ActiveWorkbook.Close savechanges:=False
wkbcurrent.Activate
Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Exit Sub
End If
Else
End If

Application.DisplayAlerts = False
wkb.SaveAs Filename:=relativePath

edited Oct 09 '18 at 23:00

answered Oct 09 '18 at 22:49

Martin Lauf

33
5

You should clean up your code and remove all instances of `.Select` and `.Selection`. These are middle operators and are not necessary for navigating from place to place. You don't have to select a workbook/sheet/range to modify it (copy/move/delete/etc). All these do is slow your code down and make it harder to follow – urdearboy Oct 09 '18 at 23:10

How to handle error when user press NO or Cancel

1 Answers1