Can we Set Default value for the Excel promt(Y/N)

[SpiderSwamy]

Hi all,

Can we Set Default value for the Excel promt, like if its asking that "
The file already exists do you want to replace the file" .

can we set the value to "yes " by default..

if someone have any idea about this, please reply me back.. I am
waiting for the reply..


thanks in advance..
Swamy

 

[Tim Williams]

application.displayalerts=false
'do stuff
application.displayalerts=true

Tim

 

[Guest]

If the prompt you're referring to is, in fact, the "system" prompt that
appears when you try to save a new file, or rename a file with an existing
filename, -you can't suppress it. Excel displays a similar prompt when you
use "SaveAs" or "SaveCopyAs", which cannot be suppressed with .DisplayAlerts.
It also displays the "Application-defined or Object-defined" error message if
the user selects "No" or "Cancel".

To avoid it from displaying, first check if the filename exists or not. If
it does, prompt the user for a new name if you don't want to overwrite the
existing file. If you do want to replace it, delete it before using "SaveAs"
or "SaveCopyAs" if it exists.

You can check if a file exists with this function:

Function bFileExists(fileName As String) As Boolean
' Checks if a file exists in the specified folder
' Arguments: fileName The fullname of the file
' Returns: TRUE if the file exists

Const sSource As String = "bFileExists()"

On Error Resume Next
bFileExists = (Dir$(fileName) <> "")

End Function

To use it requires the full path and filename passed as a string, as follows:

If bFileExists(szFullName) Then... 'do something
-OR-
If Not bFileExists Then... 'do something

where szFullName is a string variable containing the full path and filename.

HTH
Regards,
GS