VBA or other to update a cell with "x" or "y" when a file is saved in a folder

Question

I've been googling this off and on over the last few months and have not been able to find a solution. It is completely possible that I just don't have the right terms to find the results needed.

I have a list of Schools that are being reviewed. There are different stages in this review process and I have a hard time keeping track. Our database in CRM is unable to track these items. So, I have created an excel file that I manually update(hardly the best solution, and though suggestions have been made after a year there has been no improvement).

1. School names appear at the top
2. Stages of review are to the left, Many of the stages involve creating/saving a document
3. When a document is created I type "y" into the appropriate cell

What I would like to do is have the appropriate cell update with "y" when a file appears in a folder if the file's name has a portion of some identifier. Example:

• if file (with the world "Anoka" in its name) appears in Report Cards folder, B12 = y
• if file (with the word "Anoka" in its name) appears in Draft Created folder, B15 = y

I would repeat this for rows 16, 17, 18, 20, 25, 26, 27, and 28; accross all columns containing a program (the last column after programs has COUNTIFS to track total number of incompletes vs completes and other things). There are a total of 77 programs being tracked and human error leads to mistakes and takes up a lot of time when I have to go back to open the folders and search for the files and versions, etc.

Answer 1

Take a look at this for scanning your files in folder, it'll be a good start!

https://stackoverflow.com/questions/29184595/loop-on-all-files-in-the-same-directory-then-detect-extension-type/29187762#29187762

Here are some clues (I'm a bit in a hurry, I'll come back to detail if necessary)

You can can get the Path of your Workbook like that : ThisWorkbook.Path 'You get a string

You can then go to your specific folder by working on the string

Here is a code to check if the folder exist :

If Dir(Path, 16) <> ""

Look at Dir specs to have more info

And after that, I'll need to upload a VBA script (that I don't have on that computer) to have the full list of your docs in that folder and try to find "Akora" (or others) with Instr and it'll be a great procedure to reuse on other lines!

Answer 2

I think this will get you started:

To test this on your side, open a new workbook and arrange the data at same location as in the picture.

Then create a folder like this:

In each folder, create new txt files named using "AAA", "BBB" or "CCC":

Then create a new code module:

And paste this code in it:

Sub Put_Y_At_The_Right_Place()

    Dim FolderPath As String: FolderPath = "D:\Testing_Dir\"
    Dim dirTmp As String

    Dim row As Integer
    Dim col As Integer

    For row = 2 To 4

        'Each time we call "Dir" it switches to the next file it finds so we have to
        'put its result in a temp variable like "dirTmp"
        dirTmp = Dir(FolderPath & ThisWorkbook.Worksheets(1).Cells(row, 1).Value2 & "\")

        Do While Len(dirTmp) > 0 'When "Len(dirTmp) = 0" no more files were found

            For col = 2 To 4 'passing through column B to D

                If InStr(dirTmp, ThisWorkbook.Worksheets(1).Cells(1, col).Value2) <> 0 Then
                    ThisWorkbook.Worksheets(1).Cells(row, col).Value2 = "Y"
                End If

            Next col

            dirTmp = Dir 'Calling "Dir" to switch to the next file

        Loop

    Next row

End Sub

After you run this code, you should get:

To see what each line of code does, execute the code line by line using F8.

When you understand this code, try to modify it to suit your needs. If you have trouble doing that, come here and post what you've done and what the problems are.

Hope this helps!