Locating file in another directory

  • Thread starter Thread starter john-downing
  • Start date Start date
J

john-downing

I am trying to convert a Lotus 123 function into an excel fomula. In
Lotus a fuction "@isfile" will go into another directory and determine
if a file is there and return a code. Is this a way that this can be
accomplished in excel?

Again, this is the requirement. I want to have a formula or function
that will go into another directory and determine if a pre-defined file
is locted there and return a character or code to the cell. It only
needs to do this when the file is opened.

Thank You

John
(e-mail address removed)
 
john-downing said:
I am trying to convert a Lotus 123 function into an excel fomula. In
Lotus a fuction "@isfile" will go into another directory and determine
if a file is there and return a code. Is this a way that this can be
accomplished in excel?

Again, this is the requirement. I want to have a formula or function
that will go into another directory and determine if a pre-defined file
is locted there and return a character or code to the cell. It only
needs to do this when the file is opened.
Click to expand...

You'd need to use VBA to write a user-defined function. The following is an
exact work-alike to 123's @ISFILE.


Function isfile(fn As String, Optional od As Long = 0) As Long
Dim wb As Workbook

Application.Volatile

If od <> 0 And od <> 1 Then
isfile = CVErr(xlErrValue)
Exit Function
End If

If od = 1 And Dir(fn) <> "" Then
isfile = 1
ElseIf od = 0 Then
For Each wb In Workbooks
If StrComp(wb.FullName, fn, vbTextCompare) = 0 Then
isfile = 1
Exit For
End If
Next wb
End If
End Function
 
Back
Top