Check if file exists

[Jon]

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.

 

[Joel]

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

 

[Dave Peterson]

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

 

[Joel]

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]

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]

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]

It seemed to do what the OP wanted:

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.

 

[Joel]

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]

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

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

 

[Joel]

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

 

[Dave Peterson]

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.

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

 

[Joel]

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

 

[Peo Sjoblom]

It's hardly a fool proof solution is it? If Jon comes back a year from now
will he remember what to put in the formula? Is there a help file? Wouldn't
it be easier to create a button and just push that button instead of typing
in a formula? For some things a macro is much better and this is one of
them. Dave's macro will not need any path input either

Do you think it is easier to put in a path like

ifexist("C:\Documents and Settings\MySelf\My Documents\Excel
Documents\arrays2.xls")

than pushing a button?


--

Regards,

Peo Sjoblom

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

 

[Joel]

Will he remeber to push the button? the formula can be copy like any other
formula.

 

[Dave Peterson]

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.

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

 

[Jon]

Thanks Joel and Dave,

I'll admit that Dave's original post was pretty intimidating to a non-VBA
person like myself, but I do appreciate the discussion in all your posts and
I understand the pro's Dave's solution. I think they both would work for me.


I think the best option is for me to learn at least the basics of VBA. I'll
read through Dave's suggested VBA link and some of the suggested reading
materials.