Change Excel 'overwrite file?' macro

[alex3867]

Hi there

I am trying to change the standard macro that warns you if you are saving a new file as one that already exists ("A file named .... already exists in this location. Do you want to replace it?)

Basically, I am trying to change what happens when 'No' or 'Cancel' is clicked (I need to run my own macro) Is this possible, or do I need to write another similar macro that has the same function but executes before excel's default one?

Any help or links to related topics appreciated

Thanks

Alex

[Carim]

Hi,

You should remove XL warning

Application.DisplayAlerts = False

And code your own Exit scenario ...

HTH
Carim

[MattShoreson]

On the workbook save event

You could use a filesearch to check for the presence of a file called the same as the one you trying to save, if there's a match do a procedure the end, else save.

This method might still need the displayevents and would therefore still overwrite the file if you select no.

[alex3867]

Thanks for the advice both of you.

I thought I had it sussed, but I'm finding it difficult to write a code that checks if an exisiting file already has that name.

How would I go about a message box (vbYesNo) warning that executes if the filename you are trying to save as already exists in any of THREE directories!? IS this even possible?

(Otherwise, how would I simply check if the filename to be saved already exists in the CURRENT directory you are trying to save in?)

MANY thanks

Alex

[mudraker]

Alex

Here is one way to check if a file exists.

Repeat for each file and/or directory

Sub FileCheck()
If Dir("c:\Temp\Book2.xls") <> "" Then
MsgBox "File Exists"
End If
End Sub