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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,792
Messages
5,574,319
Members
412,587
Latest member
Krucial155
Top