MS Excel: Check to see if a file exists based on a value in column A in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, I have a list of part numbers in an Excel chart. I want to compare those part numbers to a directory of CAD files to see if a Drawing exists for each part number.

So if the part number from column A exists in this directory, then put a "YES" in column "B". If it does not exist, put a "NO" in column B?

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

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we have our part numbers listed in column A and we want to find out if the file exists for each of these part numbers. All of the files will be located in the R:\Symbols directory and will have a .sym file extension.

So in the case of part number 12-400047-001 we want to look for the following file:

R:\Symbols\12-400047-001.sym

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 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 = "R:\Symbols\"
    LExtension = ".sym"

    '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 part number
        Else
                'Place "No" in column B if the file does NOT exist
                If Len(Dir(LPath & Range("A" & CStr(LRow)).Value & LExtension)) = 0 Then
                    Range("B" & CStr(LRow)).Value = "No"
                'Place "Yes" in column B if the file does exist
                Else
                    Range("B" & CStr(LRow)).Value = "Yes"
                End If
        End If

        LRow = LRow + 1

    Wend

End Sub