Compare a filename in a column to see if it exist in a directory

[bobrandom123]

Hey everyone,
I'm pretty new to VBA but I think it's a great tool for parsing data which is what I've been doing a lot at work lately.

Utilizing Excel 2009, I am trying to look for a solution that will allow me to compare a column which contains a list of filenames i.e. "832423.wav" to a directory which contains those files to see "832423.wav" is present in that directory such as "C:\Users\B\Documents\Audio Files\". If there is a match then the row is highlighted. Is that possible? If so, how may I go about doing that?

I really appreciate any help I can get, this will save me a lot of time.

 

[Domenic]

Assuming that the sheet containing the file names is the active sheet, and that Column A, starting at Row 2, contains the file names, try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPath = "C:\Users\B\Documents\Audio Files\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    LastRow = Cells(Rows.Count, "a").End(xlUp).Row
    
    LastColumn = 10 [color=green]'change the ending column number accordingly[/color]
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
        strFile = Cells(i, "a").Value
        [color=darkblue]If[/color] Len(Dir(strPath & strFile, vbNormal)) > 0 [color=darkblue]Then[/color]
            Range(Cells(i, "a"), Cells(i, LastColumn)).Interior.ColorIndex = 6 [color=green]'change the color index accordingly[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[bobrandom123]

Thanks! So if the column I'm comparing is "h", just change "a" to "h" ?

 

[Domenic]

Thanks!

You're welcome!

So if the column I'm comparing is "h", just change "a" to "h" ?

That's correct...

 

[bobrandom123]

Today was an exciting day because I got to use it operationally but when that happens, people ask questions and that opens the door for enhancements

Adding onto this script, how may I go about deleting rows that do not match that criteria so if a file is not found/not highlighted, delete the entire row that pertains to the filename?

 

[Domenic]

Try...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    strPath = "C:\Users\B\Documents\Audio Files\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"
    
    LastRow = Cells(Rows.Count, "a").End(xlUp).Row
    
    LastColumn = 10 [color=green]'change the ending column number accordingly[/color]
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
        strFile = Cells(i, "a").Value
        [color=darkblue]If[/color] Len(Dir(strPath & strFile, vbNormal)) > 0 [color=darkblue]Then[/color]
            Range(Cells(i, "a"), Cells(i, LastColumn)).Interior.ColorIndex = 6 [color=green]'change the color index accordingly[/color]
        [color=darkblue]Else[/color]
            Rows(i).Delete
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Actually, since you're only going to be left with files that are found in the directory, is it still necessary to highlight these rows? If not, try the following instead...

[font=Courier New][color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
    strFile = Cells(i, "a").Value
    [color=darkblue]If[/color] Len(Dir(strPath & strFile, vbNormal)) = 0 [color=darkblue]Then[/color]
        Rows(i).Delete
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]Next[/color] i
        [/font]