Finding file names in a local folder

Solved
Report
Posts
2
Registration date
Friday June 18, 2021
Status
Member
Last seen
June 22, 2021
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,



I have a large list of file names in excel worksheet. I am trying to find which of those files are NOT in the folder on the local drive. Is there a certain VBA code or formula I can use?

2 replies

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Gogazapr,

When you have your filenames (with extention) in column A, then the code below will place an "x" in column B next to the filenames that are found.

Here is the code:
Sub RunMe()
Dim Filename, Pathname As String
Dim mFind As Range

Pathname = ActiveWorkbook.Path & "\Test Map\"
Filename = Dir(Pathname & "*")

Do While Filename <> ""
    Set mFind = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Filename)
    If Not mFind Is Nothing Then mFind.Offset(0, 1).Value = "x"
    Filename = Dir()
Loop
End Sub


Let us know if something is unclear or you want a different output.

Best regards,
Trowa
Posts
2
Registration date
Friday June 18, 2021
Status
Member
Last seen
June 22, 2021

Hello TrowaD,

I actually have my filenames without extensions. Will that work?
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Gogazapr,

It will with the code below (as long as your filenames don't contain any periods):
Sub RunMe()
Dim Filename, Pathname As String
Dim mFind As Range

Pathname = ActiveWorkbook.Path & "\Test Map\"
Filename = Dir(Pathname & "*")

Do While Filename <> ""
    Filename = Left(Filename, InStr(Filename, ".") - 1)
    Set mFind = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Find(Filename)
    If Not mFind Is Nothing Then mFind.Offset(0, 1).Value = "x"
    Filename = Dir()
Loop
End Sub


Best regards,
Trowa