Routine for checking if file exists

[Centurion]

Hello,
I am using Excel 97, and have written (by recording keystrokes)
a macro that does several things and finishes off by saving the file.
However if the saved file already exists it stops the macro and prompts
user if it's ok to overwrite the file.

I would like to........

1) how do I test (within VBA) to see if the file exists.

2) If the file does exist, then overwrite without prompt.
Or alternately, save file and incrementally add '1'
to the end of the subsequent file.

Example
File-01
File-02
File-03


Thanks in anticipation.

 

[Guest]

several ways
you could use the
Kill
command to kill the file first
and then save it so that there is no file by that name
you will get an error on the Kill command if the file does not exist, but
you can use
On error resume next
to skip the error. Look up in help index on the KILL method.
Warning. This will permantly delete any file you use it on without asking,
and can delete entire folders!

 

[Chip]

I could help you with either of those options, but another option is
simply to have it not say that prompt...it could just save over if it
exists, or save it as a new file if it doesnt-all without
asking...would that be better for you?

 

[Guest]

I have always thought there was a way to do that. How do you ahve it
overwrite without asking? because Application.Displayalerts = False has
never worked for me

 

[Centurion]

I could help you with either of those options, but another option is
simply to have it not say that prompt...it could just save over if it
exists, or save it as a new file if it doesnt-all without
asking...would that be better for you?

Yes, how do I do that.

 

[Chip]

Hmm..that is odd..it has always worked for me...can you give me your
code, and i'll test it....

 

[BBert]

On Wed, 26 Jan 2005 18:57:59 GMT, Centurion wrote...

1) how do I test (within VBA) to see if the file exists.

Sub FileExists()
Dim fFileExists As Boolean

fFileExists = (Len(Dir("C:\config.sys")) > 0)
If fFileExists = True Then
msgbox "File Exists"
Else
msgbox "No File Found"
End If
End Sub

--
Met vriendelijke groeten / Mit freundlichen Grüßen / With kind
regards/Avec mes meilleures salutations
BBert

April 20, 1986
Celtics (135) - Bulls (131)
Larry Bird: "God disguised as Michael Jordan"