Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-30-2020, 03:59 PM
ChrisOK ChrisOK is offline Error Handler If User Does Not Want to Overwrite File Windows 10 Error Handler If User Does Not Want to Overwrite File Office 2019
Advanced Beginner
Error Handler If User Does Not Want to Overwrite File
 
Join Date: Sep 2016
Posts: 51
ChrisOK is on a distinguished road
Question Error Handler If User Does Not Want to Overwrite File

How do I add the simplest method for On Error handling to STEP 6 when the user decides they do NOT want to accept the overwriting of the existing file?


(A dialog box prompts user:
"A file named 'xxxxxx.xlsx" already exists in this location. Do you want to replace it?)




OUTCOME:

If User clicks "Yes", works great, script continues to end

If clicks "No" or "Cancel" - (Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed


I've reviewed many posts but nothing with simple capability to toggle out of it and end the sub if user chooses "No" or "Cancel" as I did while testing...


Optimally, something like this would be great:
If Cancel (END SUB)
If No (prompt user with field to rename file)





Code:
Sub Sample()



'5-GIVES NEWLY CREATED SHEET A NAME
        ActiveSheet.Name = "NewTabName"


'6-SAVE RESULTS FILE TO DESKTOP
    
        ChDir "C:\Users\johndoe\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\johndoe\Desktop\NewFileFolderListName.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False


'7-GENERATE A COUNT OF DUP FINDINGS

    Const CountIFColumn As String = "D1:D500"
    Dim f As Integer
    f = WorksheetFunction.CountIf(Range(CountIFColumn), "TRUE")
    MsgBox f, vbInformation, "Count of Duplicates Found"
    
    
 '8-PROMPT USER TO NAME NEW SHEET CREATED WITH USER-ENTRY FIELD:
     'PROMPTS USER TO NAME THE SHEET
    
    Dim NewName As String
    NewName = InputBox("What Do You Want to Name This Sheet ?")
    Sheets("NewTabName").Name = NewName



End Sub
Reply With Quote
  #2  
Old 03-31-2020, 01:36 PM
BobBridges's Avatar
BobBridges BobBridges is offline Error Handler If User Does Not Want to Overwrite File Windows 7 64bit Error Handler If User Does Not Want to Overwrite File Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 675
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 04-02-2020, 05:23 PM
ChrisOK ChrisOK is offline Error Handler If User Does Not Want to Overwrite File Windows 10 Error Handler If User Does Not Want to Overwrite File Office 2019
Advanced Beginner
Error Handler If User Does Not Want to Overwrite File
 
Join Date: Sep 2016
Posts: 51
ChrisOK is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 04-03-2020, 04:50 PM
ChrisOK ChrisOK is offline Error Handler If User Does Not Want to Overwrite File Windows 10 Error Handler If User Does Not Want to Overwrite File Office 2019
Advanced Beginner
Error Handler If User Does Not Want to Overwrite File
 
Join Date: Sep 2016
Posts: 51
ChrisOK is on a distinguished road
Question Error Handling

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"
Attached Images
File Type: gif error-handling.GIF (16.5 KB, 5 views)
Reply With Quote
  #5  
Old 04-03-2020, 08:31 PM
BobBridges's Avatar
BobBridges BobBridges is offline Error Handler If User Does Not Want to Overwrite File Windows 7 64bit Error Handler If User Does Not Want to Overwrite File Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 675
BobBridges will become famous soon enoughBobBridges will become famous soon enough
Default

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?

Last edited by BobBridges; 04-04-2020 at 09:29 AM. Reason: Continuing now that I have more time
Reply With Quote
Reply

Tags
error handling, rename file

Thread Tools
Display Modes


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
Error Handler If User Does Not Want to Overwrite File How to Control Worksheet Event Handler in Module? tinfanide Excel Programming 2 10-19-2014 09:46 AM
Error Handler If User Does Not Want to Overwrite File 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft