Finding file names in a local folder

Solved/Closed
gogazapr
Posts
3
Registration date
Friday June 18, 2021
Status
Member
Last seen
February 7, 2022
- Jun 18, 2021 at 09:55 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Jun 29, 2021 at 11:54 AM
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

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 21, 2021 at 11:56 AM
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
0
gogazapr
Posts
3
Registration date
Friday June 18, 2021
Status
Member
Last seen
February 7, 2022

Jun 22, 2021 at 07:18 AM
Hello TrowaD,

I actually have my filenames without extensions. Will that work?
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Jun 29, 2021 at 11:54 AM
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
0