#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 Code:
On Error GoTo ErrHandle 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 |
#3
|
|||
|
|||
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!
|
#4
|
|||
|
|||
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" |
#5
|
||||
|
||||
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 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 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 Code:
If Err.Number = 1004 Then MsgBox "Exiting Sub" Exit Sub Else Resume End If Code:
If Err.Number = 1004 Then MsgBox "Exiting Sub" Exit Sub Else Resume End If Last edited by BobBridges; 04-04-2020 at 09:29 AM. Reason: Continuing now that I have more time |
Tags |
error handling, rename file |
|
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 |