VBA to use cell contents to loop through folder to see if filename exists

Hi All,

I am reasonably new to VBA and have only previously written simple macros (i.e. copy / paste text). I have a project at work where I am setting up a document tracking system. The basic idea is that I will have a number of folders where data files will be inserted by a range of people. The total number of files will total over 200 over the space of 4 months. Therefore, I foresee that it will become particularly hard to keep track of all the documents.

I have developed a workbook that has all of the files listed. The structure is in Column B I have assigned each file a unique number (i.e. 1.01) and in Column D the description of the document. I have done some research and it seems very possible that VBA can search through folder directories and return an answer of whether a file (within the directory) exists or not.

My challenge is that the file names will not come in a format that is aligned with Column D (i.e. the preferred file name). Files, will however, arrive with the number identifier (i.e. "1.01 File Name X"). What I wish to do is create a module that will

a) use the cell contents in Column B (i.e. file identifier) to check in a group of sub-folders whether a document exists - I want to use a "FIND" like function (where it only needs to find "1.01" and ingore the rest of the file name)

b) return for each line a value in Column E that is either "Open" (no file received) or "Closed" (file received and in folder).

c) ideally, I would also like a link to the file to be returned in Column G (either in hyperlink form or just the raw location). However, this is NOT required - this is just a "nice to have"

I have included my file which will show you what I am looking for. The subfolder structure (there is a parent folder and 8 sub-folders) would be:

Head Folder
1. Subfolder 1
2. Subfolder 2
3. Subfolder 3
4. Subfolder X...

I hope I have given you enough information guys - thank you for taking the time to read this. It would be awesome if I could work this out, and save me a substantial amount of time.

Thanks!

20131014_DataTracker.xlsx

I haven't tested it but I reckon this might work

Sub macro_1()
Dim fs, fol, sfol, fil, count, path
Set fs = CreateObject("Scripting.filesystemobject")
Set fol = fs.getfolder("C:\Mainfolderpath")
For count = 1 To Range("B" & Rows.count).End(xlUp)
    If Not Range("B" & count) = "" And Range("B" & count).Interior.ColorIndex = xlNone Then
        For Each sfol In fol.Folders
            Range("E" & count) = "Open"
            fil = Dir(sfol & "*" & Range("B" & count) & "*")
            If Not fil = "" Then
                Range("E" & count) = "Closed"
                Range("G" & count).Hyperlinks.Add Range("E" & count), fil
                Exit For
            End If
        Next sfol
    End If
Next
End Sub