Search a list of file names, do they exist on my computer

[Abraxus]

I have an excel spreadsheet with a list of files on it. Movie names, specifically.

Most of those movies I have saved on my computer, but I accidentally deleted some and need to re-rip them from my DVDs.

The name of the movie on my computer MAY have the year it came out, but I want to see if I have those movies on the computer.

For example, my spreadsheet will have "Apollo 13" but if I have the file on my computer it will be in a folder named "D:\Movies\Apollo 13 (1995)" and will be named "Apollo 13 (1995).mp4"

ALL movies will live in D:\Movies

How can I search for the existence of the files on my spreadsheet?

[HansV]

Try this function:

Code: Select all

Function Exists(MovieName As String) As Boolean
    Dim Fld As String
    Fld = Dir("D:\Movies\" & MovieName & " (*", vbDirectory)
    If Fld <> "" Then
        Exists = (Dir("D:\Movies\" & Fld & "\" & MovieName & " (*") <> "")
    End If
End Function

You can use it in a formula:

=Exists("Apollo 13")

or

=Exists(A2)

where A2 contains a movie name.

You can use it in a VBA macro too of course.

[Abraxus]

Worked perfectly, thank you!

Didn't like files with colons in them, but that's ok. I worked around it.

[BobH]

Do you use the Everything files app?

I find it quite useful for searching files on my system.