CODEWITHSUNDEEP
excelvba
Martin Lauf
Martin Lauf

Reputation: 33

How to handle error when user press NO or Cancel

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

Upvotes: 0

Views: 121

Answers (1)

Martin Lauf
Martin Lauf

Reputation: 33

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

Upvotes: 0

Related Questions