How do I check to see if images from a spreadsheet are found in a desktop folder?

[txart]

This should be an easy one..... I have a folder of 200,000+ images. I have a spreadsheet that has 40,000 items. So, I want to do two things...

First, I need to make sure that all 40,000 items in the spreadsheet are in fact in that folder. For example, the spreadsheet will have cells saying the artist name in (A2), image title in (B2), image size in (C2), and then the actual file name of the image... like IC-G405.jpg in (D2). What formula do I enter into (E2) to see if the image in (D2) is in a folder called Framed found on my desktop?

Secondly.... and this one will be more difficult.... how do I then delete the remaining images from the folder called Images that are NOT in the spreadsheet? Is that possible? Or, even just move the images from Framed that were found in the spreadsheet over to a new folder called Framed2. I just want to find and process the 40,000 images from the folder without having to pick through 200,000 images one by one. That would take forever.

Thank you for your help!!

 

[Domenic]

I think it might be easier to do it this way. First, have one folder with all your images called "Framed", as you already have. Then, for each image named in Column D, starting at D2, if the image is found in the folder, the text "Found" is entered for the corresponding cell in Column E, and the image moved to another folder, let's say it's called "Framed2". Otherwise, "Not Found" is entered. If this is acceptable, make sure that sheet containing the data is the active sheet, and try the following macro...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

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

    [color=darkblue]Dim[/color] strSourceFldr [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strDestFldr [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] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    strSourceFldr = "C:\Users\Domenic\Desktop\Framed\"
    
    [color=darkblue]If[/color] Right(strSourceFldr, 1) <> "\" [color=darkblue]Then[/color] strSourceFldr = strSourceFldr & "\"
    
    strDestFldr = "C:\Users\Domenic\Desktop\Framed2\"
    
    [color=darkblue]If[/color] Right(strDestFldr, 1) <> "\" [color=darkblue]Then[/color] strDestFldr = strDestFldr & "\"
    
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    [color=darkblue]For[/color] i = 2 [color=darkblue]To[/color] LastRow
        strFile = Cells(i, "D").Value
        [color=darkblue]If[/color] Dir(strSourceFldr & strFile, vbNormal) <> "" [color=darkblue]Then[/color]
            Cells(i, "E").Value = "Found"
            Name strSourceFldr & strFile [color=darkblue]As[/color] strDestFldr & strFile
        [color=darkblue]Else[/color]
            Cells(i, "E").Value = "Not Found"
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Where to Put the Code

1. Open the workbook in which to store the code.
2. Open the Visual Basic Editor (Developer > Code > Visual Basic or Alt+F11).
3. Insert a standard module (Insert > Module).
4. Copy and paste the above code into the module.
5. Return to Microsoft Excel (File > Close and Return to Microsoft Excel or Alt+Q).
6. Save the workbook.

How to Use the Macro

1. Display the Macro dialog box (Developer > Macro or Alt+F8).
2. Click/select the macro called "test".
3. Click/select "Run".