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

User avatar
Abraxus
3StarLounger
Posts: 263
Joined: 01 Mar 2010, 17:34
Location: Blue Springs, MO

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

Post by 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?
Morgan

User avatar
HansV
Administrator
Posts: 79561
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

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

Post by 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.
Best wishes,
Hans

User avatar
Abraxus
3StarLounger
Posts: 263
Joined: 01 Mar 2010, 17:34
Location: Blue Springs, MO

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

Post by Abraxus »

Worked perfectly, thank you!

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

User avatar
BobH
UraniumLounger
Posts: 9589
Joined: 13 Feb 2010, 01:27
Location: Deep in the Heart of Texas

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

Post by BobH »

Do you use the Everything files app?

I find it quite useful for searching files on my system.
Bob's yer Uncle
(1/2)(1+√5)
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs