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

txart

New Member
Joined
Oct 15, 2011
Messages
14
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!!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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...

Code:
[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".
 
Upvote 0

Forum statistics

Threads
1,224,889
Messages
6,181,605
Members
453,055
Latest member
cope7895

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top