Check if files exist

[Hawjeen]

Hi,

Could someone help me with writing a macro that, could check of some files already exist?

Basically i have different file names (PDF) in column A(100+ rows), that i want to check if they exist in a folder.
if they do i want the cell next to it in column b to be green and write "OK"
if not then i want it to be red and write "missing"

i hope someone could be some kind to help me

BR
Hawjeen

 

[jkpieterse]

Select cell B1
Define this range name (Control+F3, click New...)
Name: FileExists
Refers to: =FILES(A1)
Click OK
Now enter this formula next to your filename:
=IF(ISNA(FileExists),"Missing","Exists")

 

[JackDanIce]

What is the full path of the folder to check?

 

[Hawjeen]

It's "C:\Bonus".

 

[JackDanIce]

Try:

Sub FileTest()

    Dim x       As Long
    Dim arr()   As Variant
    Dim fso     As Object
    Const sPath As String = "C:\Bonus\"

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With ActiveSheet
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(1, 2).Resize(x).Value = "Missing"
        arr = .Cells(1, 1).Resize(x, 2).Value
        
        For x = LBound(arr, 1) To UBound(arr, 2)
            If fso.fileexists(spath & arr(x, 1)) Then arr(x, 2) = "OK"
        Next x
        
        .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    End With
    
    Erase arr
    Set fso = Nothing
    
End Sub

 

[Hawjeen]

Thank you so much!

have a nice weekend

 

[Hawjeen]

Hi again,

Sorry but i tried to modify it a bit, but i cant seem to make it work, as i intend and i hope you could help out again

1. the filename i need to look up is based on a formula, and whenever i run the macro it converts it to a value can i somehow bypass that, so it is still a formula after the macro is run?
2. how can i make it start from row 4 instead of 1, i have tried to change it but i seem to do it wrong.

and as a bonus question, lets say i want the result to be in column D instead of B, where should i change it?

sorry for all the stupid questions, but im not confident when working with scripting dictionaries even though i love them for being so fast

 

[JackDanIce]

You're welcome

 

[shg]

I think Jan Karel's suggestion was pretty clever. I wasn't aware of the XL4 FILES function.

The workbook still needs to be saved as an xlsm file.

 

[Hawjeen]

Hej Jack,

Could You help me with those 2 problems i had?

BR
Hawjeen