Error Handler If User Does Not Want to Overwrite File

BobBridges · #1 03-31-2020, 01:36 PM

One way is to ask the user yourself, using MsgBox, maybe like this:

Code:

If File_Already_Exists Then
  OprReply = MsgBox("The Excel workbook " & filename & " already exists.  Do you want to write over" _
    & " it?",vbYesNoCancel + vbQuestion, "Filename conflict!"
  Select Case OprReply
    Case vbYes 'turn off the warning and SaveAs again
    Case vbNo 'cancel the save
    Case vbCancel 'I dunno, whatever you want to do if he hit Cancel
    End Select

In your case, though, that involves detecting ahead of time that the file exists, probably using FileSystemObject. Simpler, in my opinion, to tell VBA to spot the error when it happens, like this: 1) Before you get to this part of the program—I always do it first thing in a module—set error trapping like this:

Code:

On Error GoTo ErrHandle

That tells VBA that when there's an error—any error—instead of handling it in the normal way, VBA should go to the statement label you name. (I always call it "ErrHandle" but you can name it anything you like.) Then, down near the bottom of the program 2) add the part where you handle the error, like this:

Code:

  Exit Sub

ErrHandle:
  If Err.Number = 1004 Then
    'do whatever you want to do if the user rejected the SaveAs
  Else
    Resume
    End If

You have to add the Exit Sub statement before the label, or your code will fall into these statements after it's finished normally. The Resume statement, if the error wasn't 1004, means in that case go back to the statement that caused the error and do it again, and this time abend in whatever way VBA considers normal for that error.

ChrisOK · #2 04-02-2020, 05:23 PM

Thanks greatly Bob, got pulled off to a more urgent project; will test this hopefully by EOD tomorrow or first part of next wk and get back w/ you with any issues!

ChrisOK · #3 04-03-2020, 04:50 PM

Ok Bob, finally got a chance to test it tonight - and appears I still have something wrong.. This error handling is new..

but likely important to start incorporating..

I put the On Error part at the very top (just below the name of the Sub) as directed... then added the ErrHandle: piece below the 'SAVE RESULTS FILE' section

Getting a compile error: Else without If (screenshot attached)

If the user chooses "Yes" then I'd like it to continue w/ scripts
If the user chooses "No" or "Cancel" (which is giving the 1004 error) l'd like the script to just end w/ a MsgBox "Exiting Sub"

BobBridges · #4 04-03-2020, 08:31 PM

Ah, you're new to VBA, I see

. Don't sweat it; everyone has to start out as a noob, and this one is easy.

Take a look again at the code as you wrote it. It differs in a few ways from what I suggested, and some of the differences are important:

Code:

ErrHandle:
    If Err.Number = 1004 Then Exit Sub
    MsgBox "Exiting Sub"
    Else
    Resume
    End If

Let's look at the important differences first:

1) The way I wrote it went like this:

Code:

If Err.Number = 1004 Then
  <do one thing>
Else
  <do a different thing>
  End if

Your If statement says simply "If Err.Number = 1004 Then <do something>. The difference is that you have the first thing you want to do on the same line as the If-Then part. That may seems like a small, picky difference, but in VBA it makes all the difference. If you look up the language documentation, you'll see it offers two formats; when you put it all on one line, that one line is all you get to say about it.

So that explains the actual error message: You said "if <something is true> then <do something>", and that was the end of the If statement—so when it later saw an End If, it didn't understand why. Let's fix that one problem:

Code:

If Err.Number = 1004 Then
  Exit Sub
  MsgBox "Exiting Sub"
  Else
  Resume
  End If

2) Another important error: You told to Exit Sub, and then you told it in the following statement to do the MsgBox message. But by that time it would already have ended the program. If you want it to display the message, naturally you want it to do so before it exits the program. Let's move Exit Sub:

Code:

If Err.Number = 1004 Then
  MsgBox "Exiting Sub"
  Exit Sub
  Else
  Resume
  End If

That doesn't do what you want, yet. What you want is to ask the user what to do in case of a 1004 error, and all this does is display a message saying "Exiting Sub" and then quitting. But at least it'll do that much. Before we fix that, let's talk about an unimportant (well, less important) problem. VBA doesn't care about indentation, but your eyes do. I think most experienced programmers will tell you this too: There are several decent ways to indent your code so that you can easily tell which statements are "subordinate" to others. You can indent two spaces, or four; you can indent the End along with the other statements in a block, or with the If and Else. You can pick any of a number of styles. But pick one! This is how I do it:

Code:

If Err.Number = 1004 Then
  MsgBox "Exiting Sub"
  Exit Sub
Else
  Resume
  End If

This is getting kind of long, so we'll tackle the next part in a separate post. With me so far?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MS Word Error: This file is in use by another application or user	conky	Word	4	02-26-2019 09:21 AM
How can I get Outlook from one user to another w/o a .pst file (primary user on computer is corrupt)	Tenmakk	Outlook	0	03-01-2015 12:49 PM
How to Control Worksheet Event Handler in Module?	tinfanide	Excel Programming	2	10-19-2014 09:46 AM
Stop macro if no file is selected in dialog box (when user presses cancel instead of selecting file)	spencerw.smith	Word VBA	2	08-12-2014 07:56 AM
Word does not ask do i want to overwrite a file on saving a file	sam2149	Word	2	03-24-2014 04:18 AM