Another problem is when your function recalcs.

If you delete an existing file named in column A, when does your function get
reevaluated.

Or if you add a file to the folder that wasn't there before.

With things like this, I wouldn't want to make the function volatile--especially
with a large list. I would much rather just run the macro when I knew that I
needed a refreshed list.

On top of that, I'm not sure I'd use your function without some changes:

Function ifexist(Target As String) As String
ifexists = Dir(Target)
End Function

The typo is the first thing I'd fix.

Second, if you pass a non-existing UNC path, what's returned?

I don't think "automatic" is always better.

Joel wrote:

The difference is one is automatic and one is manual. For somebody who
doesn't know VBA it is better to make the function automatic.

If Jon comes back a year from know and makes changes will he remember that
he has to run a macro to get updates? Lets supply people who request help
"Fool-Proof solutions where possible"!

"Dave Peterson" wrote:

I think it comes down to what the OP meant by an excel formula. For someone who
wrote that he didn't know how to do it in VBA, I would think that he meant using
functions built into excel--no VBA allowed.

Once the line of VBA is crossed, I don't see too much distinction between a UDF
and a subroutine in a case like this.

Joel wrote:

CAn you tell me what is the difference between a spreadsheet formula and a
VBA function?

"Dave Peterson" wrote:

I think you're stretching the "excel formulas" portion of the original post to
include a UDF.

Joel wrote:

Dave: Jon said "I can't seem to find a way to do it with Excel formulas". I
simply found a way to do it witth a formula (excel function).

"Dave Peterson" wrote:

It seemed to do what the OP wanted:

I will
have a few hundred file names in column A, and in column B I just want to
have a formula that equals "Yes" if the file exists on my LAN or "No" if it
doesn't.


Joel wrote:

I didn't look carefully. Thougt you wrote a function. For somebody who
doesn't know VBA, I thought giving a simple function was the best approach.
Your code could be a problem because it writes the results in a 2nd cell
which could overwrite other data.

"Dave Peterson" wrote:

Each cell in A1:Axx will have a corresponding message in column B saying if the
file exists.

Why doesn't looping through the cells make sense?



Joel wrote:

Dave: What happens if some cells have filenames that exists and others
don't? Looping through cells in this case doesn't make sense.

"Dave Peterson" wrote:

You could use a macro:

Option Explicit
Sub testme01()
Dim wks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim TestStr As String

Set wks = Worksheets("sheet1")
With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

On Error Resume Next
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
TestStr = ""
TestStr = Dir(myCell.Value)
If TestStr = "" Then
myCell.Offset(0, 1).Value = "Doesn't exist"
Else
myCell.Offset(0, 1).Value = "It currently exists"
End If
End If
Next myCell
On Error GoTo 0

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jon wrote:

Is there an easy way in Excel to check if a file exists? I can concatenate
strings and build the full path and name of the file I want to check. I will
have a few hundred file names in column A, and in column B I just want to
have a formula that equals "Yes" if the file exists on my LAN or "No" if it
doesn't.

I can't seem to find a way to do it with Excel formulas. I'm sure this is
trivial in VBA, but I don't know it.

Thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson