MS Excel: Check to see if files exist based on values from 2 columns in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I need to check if certain pdf files are in a directory. The filename format for the pdf files to check for is:

Ingredient number + {space} + NUT.pdf
Ingredient number + {space} + SPC.pdf

For example,

101 NUT.pdf
101 SPC.pdf

In column A of the spreadsheet, I have an ingredient number that will be used to check for each filename. In column B, I want to display "Yes" if the NUT filename exists for the corresponding ingredient number, otherwise it should display "No". And in column C, I want to display a "Yes" if the SPC filename exists for the corresponding ingredient number, otherwise it should display "No".

Answer: Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we have our ingredient numbers listed in column A and we want to find out if the file exists for NUT filename as well as the SPC filename. All of the files will be located in the C:\Ingredients directory and will have a .pdf file extension.

So in the case of ingredient number 101, we want to look for the following file in column B:

C:\Ingredients\101 NUT.pdf

And we want to look for the following file in column C:

C:\Ingredients\101 SPC.pdf

You can run the macro by select Macro > Macros under the Tools menu.

Then select the macro called CheckIfFileExists and click on the Run button.

After you run the macro, the values in column B and column C should indicate whether the files exist or not.

You can press Alt-F11 to view the VBA code.

Macro Code:

The macro code looks like this:

Sub CheckIfFileExists()

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

    'Initialize variables
    LContinue = True
    LRow = 2
    LPath = "C:\Ingredients\"
    LExtension = ".pdf"

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

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

        'Check if file exists for each ingredient
        Else
            ' ---- NUT file ----
            'Place "No" in column B if the file does NOT exist for NUT file (ie: 101 NUT.pdf file)
            If Len(Dir(LPath & Range("A" & CStr(LRow)).Value & " NUT" & LExtension)) = 0 Then
                Range("B" & CStr(LRow)).Value = "No"
            'Place "Yes" in column B if the file does exist for NUT file (ie: 101 NUT.pdf file)
            Else
                Range("B" & CStr(LRow)).Value = "Yes"
            End If

            ' ---- SPC file ----
            'Place "No" in column C if the file does NOT exist for SPC file (ie: 101 SPC.pdf file)
            If Len(Dir(LPath & Range("A" & CStr(LRow)).Value & " SPC" & LExtension)) = 0 Then
                Range("C" & CStr(LRow)).Value = "No"
            'Place "Yes" in column B if the file does exist for SPC file (ie: 101 SPC.pdf file)
            Else
                Range("C" & CStr(LRow)).Value = "Yes"
            End If
        End If

        LRow = LRow + 1

    Wend

End Sub