Excel code for File Exist Issue

Question

I am not much familiar with VBA code in Excel.

I have got this code for my task- to check file exist in folder and if yes- it will show "Yes" with conditional formula.

The code is working fine except one issue- Its not automatically updating the file when present, specially when the Excel is open.

The code is

Function FileExists(full_path As String)

Dim fso_obj As Object

Set fso_obj = CreateObject("Scripting.FileSystemObject")

FileExists = fso_obj.FileExists(full_path)

End Function

Using this conditional formula to show "Yes"

=IF(FileExists(K5),"YES", "")

Thanks in advance

Answer 1

Function FileExists(full_path As String) As Boolean
Static fso_obj As Object
Application.Volatile
If fso_obj Is Nothing Then
Set fso_obj = CreateObject("Scripting.FileSystemObject")
End If
FileExists = fso_obj.FileExists(full_path)
End Function

Answer 2

Thanks Andreas,

Still some of the files I need to manually click on the conditional formula to update.

Second thing I have pasted the code in Module of This Workbook. Is it the right path?

Answer 3

1. remove your existing code!
2. the code must be placed in a regular module
3. such things work only if called as UDF from a cell

Answer 4

I did the removal of my previous code and placed your code.

And yes, it's in the regular module of the workbook.

What I found here is if I write the code (=IF(FileExists(C2),"YES","NO",) in the cell, the excel is not automatically updating if the file exists or is not exist in the concerned folder. (Column-E in below image-File-2.docx exist but the cell value is not updating automatically).

Need to click some cells in excel to update the excel. Sometimes it works & sometimes not.

However, I also found that if the code (=FileExist(A2)), is entered in conditional formatting with highlighting color, then the code is working perfectly. (Column-D in below image- both file exists in folder)

By the way, thanks for the help.