Linking workbook cells with a UDF

[Stoodwalk]

I apologize for those who saw this posting in one of the
other groups today. I felt that this forum maybe better
suited for this question.

I would appreciate any help concerning links to Workbooks
that don't exist yet.

I have workbook that compiles the results of other
workbook files that are sent in from elsewhere. I can
predict the name of the workbook file, but I don't want to
create a lot of dummy books.

I have created a UDF to check if a file exists that
returns a true or false. unfortunately when I use a file
path in an IF statement in a cell excel attempts to locate
the file even if the condition is false. I then have to
cancel on every OPEN dialog for every book Excel attemps
to find. (this ends up being a major Headache!)

Here is my UDF code:
Public Function FileExists(fname) As Boolean

' Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True _
Else FileExists = False
End Function

Here is my cell formula:
=IF((FileExists(B9)=FALSE),"No file",'C:\Documents and
Settings\UserX\My Documents\Excel test\[Book1.xls]Sheet1'!
$A$3)

I am open to other suggestion as well, my appraoch may not
be the correct way to handle this.

Thanks.

 

[Kevin Stecyk]

Stoodwalk,

=IF(FileExists(B9),'C:\Documents and Settings\UserX\My Documents\Excel
test\[Book1.xls]Sheet1'!$A$3, "No file")

You can shorten it to the above. FileExists(B9) is either TRUE (which case
you want your A3), or it is FALSE (which case you want "no file").

HTH

Best regards,
Kevin