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?
Search a list of file names, do they exist on my computer
-
- 3StarLounger
- Posts: 263
- Joined: 01 Mar 2010, 17:34
- Location: Blue Springs, MO
-
- 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
Try this 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.
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
=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
Hans
-
- 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
Worked perfectly, thank you!
Didn't like files with colons in them, but that's ok. I worked around it.
Didn't like files with colons in them, but that's ok. I worked around it.
Morgan
-
- 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
Do you use the Everything files app?
I find it quite useful for searching files on my system.
I find it quite useful for searching files on my system.
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |