VBA Code To Search Files in Folder

[Jar888]

Got a stump of a question. Is there a way to search for files in the folder as shown in the attachment based off column A using VBA and either highlight the cell to indicate that that file exists or copy to a new directory? To give you an idea, I am assuming 98% of these files don't exist but I have over 9000 to check through.

As you can see, file names have an underscore prior to the order number displayed.

 

[Kerryx]

You could use this to get all the file names from your archive folder into excel an then do a match or vlookup with the info

Sub Get_FileNames()
Dim j As Long
    Dim xDirect$, xFname$, InitialFold
    Dim fldpath
 

    InitialFoldr$ = "C:\"    '<<< Startup folder to begin searching from

    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder to list Files from"
        .InitialFileName = InitialFoldr$
        
        .Show
        fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
 
        Workbooks.Add
Cells(1, 1).Value = fldpath
Cells(2, 1).Value = "FILE NAME"

Set fso = Nothing
Range("a1").Font.Size = 9
ActiveWindow.DisplayGridlines = True
Range("a3:e" & Range("a2").End(xlDown).Row).Font.Size = 9
Range("a2:e2").Interior.Color = vbYellow
Columns("c:h").AutoFit
Application.ScreenUpdating = True


        If .SelectedItems.Count <> 0 Then
            xDirect$ = .SelectedItems(1) & "\"
            xFname$ = Dir(xDirect$, 7)
            Do While xFname$ <> ""
            j = Range("A1").End(xlDown).Row + 1
Cells(j, 1).Value = xFname$
        
                xFname$ = Dir
          
            Loop
         End If
        
    End With

    ActiveWorkbook.Save
End Sub