Finding file names in a local folderSolved/Closed

Question

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?

TrowaD · Answer

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

TrowaD · Answer

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

Finding file names in a local folder

2 replies

Similar discussions

Subscribe To Our Newsletter!