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