VBA check if file exists in sub folders

Question

I am relatively amateur at VBA and am using a code provided by tech on the net.

I have an Excel document with files names in column B (not always one file type) which I am trying to ensure I have copies and the correct revision in a designated folder.

Currently, the code works perfectly for a specific folder location, but the files referenced in the Excel spreadsheet exist in various other folders and thus I need to create a code that can search a main folder and loop through the various sub-folders.

See current code below for reference.

Sub CheckIfFileExists()

    Dim LRow As Integer
    Dim LPath As String
    Dim LExtension As String
    Dim LContinue As Boolean

    'Initialize variables
    LContinue = True
    LRow = 8
    LPath = "K:\location\main folder\sub folder \sub folder"
    LExtension = ".pdf"

    'Loop through all column B values until a blank cell is found
    While LContinue

        'Found a blank cell, do not continue
        If Len(Range("B" & CStr(LRow)).Value) = 0 Then
            LContinue = True

        'Check if file exists for document title
        Else
            'Place "No" in column E if the file does NOT exist
            If Len(Dir(LPath & Range("B" & CStr(LRow)).Value & LExtension)) = 0 Then
                Range("E" & CStr(LRow)).Value = "No"
            'Place "Yes" in column E if the file does exist
            Else
                Range("E" & CStr(LRow)).Value = "Yes"
            End If


        End If

        LRow = LRow + 1

    Wend

End Sub

There are over 1000 documents, so simple windows searches is not ideal, and I have reviewed several previous questions and cannot find an answer that helps.

See [this](http://stackoverflow.com/documentation/vba/990/scripting-filesystemobject/10411/recursively-enumerate-folders-and-files#t=201701110450381295929) and [this](https://msdn.microsoft.com/en-us/library/5xc78d8d(v=vs.84).aspx). — Comintern, Jan 11 '17 at 04:52
see link. Posted this a while back. Look for my answer i posted to the question — mojo3340, Jan 11 '17 at 09:06
Thanks all. @mo.h I am trying to combine your previous answer with my desired actions as per above. Not having a lot of luck doing so, but the idea looks to be what I am trying to achieve. — pjanelson, Jan 11 '17 at 23:03

score 2 · Answer 1 · answered Jan 12 '17 at 09:18

Okay, my answer is going to revolve around 2 comments from your question. This will serve only as a basis for you to improve upon and adapt to how you need it.

N.B SKIP TO THE BOTTOM OF MY ANSWER TO SEE THE FULL WORKING CODE

The first comment is:

I need to create a code that can search a main folder and loop through the various sub-folders.

The code i will explain below will take a MAIN FOLDER, that you will need to specify, and then it will loop through ALL subfolders of the parent directoy. So you will not need to worry about specific sub folders. As long as you know the name of the file you want to access, the code will find it regardless.

The second is a line of your code:

LPath = "K:\location\main folder\sub folder \sub folder"

This line of code will form part of a UDF (User Defined Function) that i will display below.

Step 1

Re-label LPath to be the what is called the "Host Folder". This is the MAIN FOLDER. For Example: Host Folder = "K:\User\My Documents\" (Note the backslash at the end is needed)

Step 2

Set a reference to Microsoft Scripting Runtime in 2 places:

i) In the code

Set FileSystem = CreateObject("Scripting.FileSystemObject")

ii) In the VBA Editor. (To a basic google search on how to find the reference library in the VBA editor)

Step 3

This is the main element, this is a sub routine that will find the file no matter where it is, providing a file name and host folder has been provided.

Sub DoFolder(Folder)

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        If File.Name = "Specify Name.pdf" Then
            Workbooks.Open (Folder.path & "\" & File.Name), UpdateLinks:=False
            Workbooks(File.Name).Activate
            Exit Sub
        End If
    Next

End Sub

The code above will simply open the file once it has found it. This was just my own specific use; adapt as necessary.

MAIN CODE

Option Explicit
Dim FileSystem As Object
Dim HostFolder As String

Sub FindFile()
HostFolder = "K:\User\My Documents\"

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

End Sub
Sub DoFolder(Folder)

    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    For Each File In Folder.Files
        If File.Name = "Specify Name.pdf" Then
            Workbooks.Open (Folder.path & "\" & File.Name), UpdateLinks:=False
            Workbooks(File.Name).Activate
            Exit Sub
        End If
    Next

End Sub

You can chop this up how you see fit, you can probably throw it into your sub CheckIfFileExists() or just use it on its own.

Let me know how you get along so i can help you understand this further

This is useful for me as well but I want to get filename from cell and write the output/result back in result column in respective row. I tried to convert this in function with return value but because of recursive call in DoFolder it's not working as expected for me. if you have any suggestion please help me out. Thank you! — Vahida Vadiya, May 28 '23 at 17:43

VBA check if file exists in sub folders

1 Answers1