Finding file names in a local folder

Report
Posts
2
Registration date
Friday June 18, 2021
Status
Member
Last seen
June 22, 2021
-
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 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
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
469
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
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
469
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!