Prevent save if file exists

KW1M

New Member
Joined
Jul 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks to many here; I have a macro working very well to save as a new workbook, clear data, etc... I've searched and haven't found what should be simple.

What I need to prevent now is when someone accidently hits the "Start New Session" macro button I built a second time -- today, it does a save-as of the current worksheet with today's date.

Even though a pop up is displayed saying "do you really want to overwrite" -- sometimes it happens...

I'd like the macro to detect a file already exists, displays a message like "file exists, exiting" and not allow a new file to be generated. (Reason - this macro clears out a ton of entered data from the previous week, and if they click yes - it's painful.)

Thanks, Martin in NH
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,403
Try the following code...

VBA Code:
    If Len(Dir(saveas_filename, vbNormal)) > 0 Then
        MsgBox "File already exists, exiting...", vbExclamation, "File Exists"
        Exit Sub
    End If

...where saveas_filename contains the path and filename.

Hope this helps!
 
Solution

KW1M

New Member
Joined
Jul 21, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Works like a charm -- THANK YOU!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,477
Members
412,595
Latest member
slim313
Top