MS Excel 2003: Check to see if a file exists based on a value in column A
This Excel tutorial explains how to write a macro to check to see if a file exists based on a value in Excel 2003 and older versions (with screenshots and step-by-step instructions).
Question: In Microsoft 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 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
Advertisements