If File Exists, WITHOUT VBA

[MarkBoy]

My searching has proven that there are hundreds of threads across the net dedicated to how to use VBA to check whether or not a file exists. However, for my current purpose I really need this to be a cell-based formula.

Is this acheivable at all? I don't need to create the file or anything complicated, I just need to know if it's there...

 

[Von Pookie]

You could always write a macro that you can use as a worksheet function:

http://www.themssforum.com/ExcelMisc/Check-file-726245/

 

[MarkBoy]

Ok, that seems to be the startings of a good solution, but I keep getting #VALUE errors.

I have created a module with the following code:

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

and the worksheet formula:

=IF(ifexist(TEXT("C:\Users\DEFAULT\Pictures\BatchPictures\" & G2 & ".jpg",)),TEXT(G2 & ".jpg",),"")

Any ideas what might be wrong with my implementation?

FYI, I have tried a more basic implementation of the formula, which has also failed, but as above is how I would like it eventually implemented. The basic version which failed was:

=IF(ifexist("C:\Users\DEFAULT\Pictures\BatchPictures\1783756.jpg"),"TRUE","FALSE")

 

[Scott Huish]

Yes, the function you are using is returning a string, the expectation would be that if it returned "" the file would not exist. You are using the function as if it were returning a Boolean value, and the modification to use it that way is here:

Function ifexist(Target As String) As Boolean
Application.Volatile
ifexist = (Dir(Target) <> "")
End Function

You also don't need the TEXT function:

=IF(ifexist("C:\Users\DEFAULT\Pictures\BatchPictures\" & G2 & ".jpg"),G2 & ".jpg","")

 

[MarkBoy]

Perfect - works like a charm!

Thanks for all the help. My brain was just refusing to Debug this morning.

 

[MarkBoy]

Ok - One more problem!

It all works fine, but for some reason, the formulae don't update. It shows correctly that the file isn't there, but when I put the file there, it doesn't update to show that it is. Recalculating doesn't fix it either, and neither does saving, closing, and reopening, which would usually force a full recalculation. However, if I edit the formula, don't alter it, and press Enter, then it DOES update.

Any idea how I can have this happening automatically? It doesn't have to be instantaneous, but having to edit every formula every time isn't satisfactory...

 

[Scott Huish]

I had edited the post earlier to account for that, see above. You then hit F9 to recalculate.