Open file, if file exists

Hi,

I am trying to write a script that will run a macro on a number of files if they exist.
If they do not, it will look for another the next pre-defined file and so on, until all 16 files have been searched for and the macro run, where applicable.

e.g.

If file "C:\My Documents\Book1.xls" exists then run macro
then
If file "C:\My Documents\Book2.xls" exists then run macro

But am not sure how to combine the "If file exists" and "if file does not exist" functionality.

Anyone able to assist?

Please Login or Register  to view this content.

Thanks Sadath31, but rather than produce a message box to inform me the file isn't there, I would like it to look for the next file?
In addition, if the file is there, run the macro, then look for the next file and repeat.

I'm pressuming that this should be run in some sort of loop?

Many thanks

Assume you have filenmae with extension (.xls) in range A1:A100
(sorry I am not tested the macro)

Sub FileOpen()
For Each Fname In Range("A1:A100")
With Application.FileSearch
.NewSearch
.LookIn = "D:\"
.Filename = Fname
.Execute
If .Execute() > 0 Then
Call XYZ 'Macro name to be run
Exit Sub
End If
End With
Next Fname
End Sub