Check if file exist within a hardrive (folder and subfolders)

[pcampos]

I have an excel table with a large amount of file names (PDFs) and their description, as part of a list of documents that had to be delivered.
All those files are in a folder (hard drive) organized within many subfolders, and I would like to check whether the documents they presented as delivered are actually in the hard drive.
Hence, I need to create a macro that looks through all subfolders of a file if a specific name exists, and as I'm probably not the first one to have this problem, I'd figure I'd ask.

Anyone seen/created a macro that applies here?

Thanks!
Pablo

 

[pcampos]

I found a code that could work, but I have one problem with it.
I created a an example with txt files spread in several subfolders. The code gets the file name from a cell and writes a 1 in the cell next to it if the folder was found
The problem is that it only finds one cell per subfolder. I'm trying to figure out how to properly set the code, but I can't find a way so far. Does anyone know?
I tried to structure it so only after the first file was searched it would to the second one. I don't know why, but this is not the case...

Option Explicit
Dim FileSystem As Object
Dim HostFolder As String


Sub FindFile()
HostFolder = "C:\etcetcetc"


Set FileSystem = CreateObject("Scripting.FileSystemObject")
DoFolder FileSystem.GetFolder(HostFolder)


End Sub


Sub DoFolder(Folder)
Dim SubFolder
Dim i As Integer
Dim Filename As String
For i = 1 To 4
Filename = Cells(2 + i, 2).Value
For Each SubFolder In Folder.SubFolders
DoFolder SubFolder
Next
Dim File
For Each File In Folder.Files
If File.Name = Filename Then
Cells(2 + i, 3).Value = 1
Exit Sub
End If
Next
Next i




End Sub

Thanks!

 

[NoSparks]

You could create a list of all files on the drive and work from there via filtering etc.

I came across this macro quite some time ago and tucked it away for future use.
It lists all existing files in the starting folder and its sub-folders to the active sheet.
You just need to put in your starting folder.

Sub ListFiles()
    Dim objFSO As FileSystemObject
    
    'Insert the headers for Columns A through F
    Range("A1").Value = "File Name"
    Range("B1").Value = "File Size"
    Range("C1").Value = "File Type"
    Range("D1").Value = "Date Created"
    Range("E1").Value = "Date Last Accessed"
    Range("F1").Value = "Date Last Modified"
    
    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Call the RecursiveFolder routine with this start location
    Call RecursiveFolder(objFSO, "your start folder goes here", True)
    
    'Change the width of the columns to achieve the best fit
    Columns.AutoFit
    
End Sub


Private Sub RecursiveFolder(FSO As FileSystemObject, _
                            MyPath As String, _
                            IncludeSubFolders As Boolean)

    'Declare the variables
    Dim File As File
    Dim Folder As Folder
    Dim SubFolder As Folder
    Dim NextRow As Long
    
    'Find the next available row
    NextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'Get the folder
    Set Folder = FSO.GetFolder(MyPath)
    
    'Loop through each file in the folder
    For Each File In Folder.Files
        Cells(NextRow, "A").Value = File.Name
        Cells(NextRow, "B").Value = File.Size
        Cells(NextRow, "C").Value = File.Type
        Cells(NextRow, "D").Value = File.DateCreated
        Cells(NextRow, "E").Value = File.DateLastAccessed
        Cells(NextRow, "F").Value = File.DateLastModified
        NextRow = NextRow + 1
    Next File
    
    'Loop through files in the subfolders
    If IncludeSubFolders Then
        For Each SubFolder In Folder.SubFolders
            Call RecursiveFolder(FSO, SubFolder.Path, True)
        Next SubFolder
    End If
    
End Sub

 

[pcampos]

Thanks! That actually will be very useful, I believe I will be asked to provide a list of documents within the folder.
In case someone also needs it, I found another code that looks for file, I included a cell with the number of times the file was found and the location. The coded includes the youtube video where I found it.

Sub Find_Files()
'https://www.youtube.com/watch?v=ddA2_SOaq14

Dim FileSystem As Object
f = "C:\Location"
Dim Filename As String

For i = 1 To 9
Filename = Cells(2 + i, 2).Value
On Error GoTo ext

sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & f & Filename & """ /s /a /b").stdout.readall, vbCrLf)


Cells(2 + i, 3).Value = UBound(sn)
Cells(2 + i, 4).Resize(UBound(sn) + 1) = Application.Transpose(sn)




ext:


Next i
End Sub

 

[NoSparks]

Ah, yes.... the array variable name tells me where that was adapted from,
a very informative site well worth bookmarking.