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.

Here is a 1 instructtion VBA function that you can use.
Install as follows from speadsheet menu

1) from spreadsheet: Tools - Macro - Visual Basic Editor
2) from VBA: Insert - Module
3) Copy code below and paste into VBA window
4) Add code to cell: =ifexist("C:\abc.xls")
You can use any string or cell reference. The code will work with wildcards
as well.


Function will return the filename if it exists or "" if file is not found.
=if(ifexist("C:\abc.xls")= "","File Not found","File Found")


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

"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.

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: 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

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

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

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