Linking to other workbooks

[Stoodwalk]

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

I have workbook that compiles the results of other
workbooks that are sent in from elsewhere. I can predict
the name of the workbook, 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.

 

[Julia0001]

Hi there

You could try putting all of that logic inside a UDF, e.g. have a function called DisplayLink which takes the parameters FileName, SheetName and CellRef and includes a call to your existing function FileExists.

Then your worksheetcell would just have =DisplayLink(B9,"Sheet1","$a$3") and the UDF does all the rest of the work.

Hope this helps!

Julia

 

[Guest]

Thank you for your reply, I think that is a good way to do
it.

Stoodwalk

-----Original Message-----
Hi there

You could try putting all of that logic inside a UDF,

e.g. have a function called DisplayLink which takes the
parameters FileName, SheetName and CellRef and includes a
call to your existing function FileExists.

Then your worksheetcell would just have =DisplayLink

(B9,"Sheet1","$a$3") and the UDF does all the rest of the
work.