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