1.

Solve : Automatic Deletion of Files With Spreadsheet?

Answer»

Hello, I am trying to make my life easier at the moment, I have multiple hard-drives and computers containing lots and lots of image files. I have also created an excel spreadsheet with all of these images' information (such as image #, date the image was acquired, type of camera used etc...). However after the images are processed there are many that need to be rejected, these rejected images are manually noted in the Spreadsheet. However I am now looking to create a batch file (or any other possible solution) that looks at the excel spreadsheet, finds out which items are rejected, and then deletes these items from their respective windows folder. Is this possible?

Any help on this would be greatly appreciated. I am a beginner programmer looking to learn a thing or two so hopefully someone can help me out?

Thanks yes, it is possible, the whizzy answer would be to have a vba macro that goes out and deletes the files for you

however, you say you are still on the lower slopes of the learning curve, so lets take a more direct route.

you say you can identify the rejected images -- filter your spreadsheet to show only the rejected ones

save as a text file only the column holding the filenames (with their paths)

all you need now is a simple 1 line command that reads through the file, deleting each filename as it is read

suppose your text file is called rejects.txt, then
Code: [Select]For /f "delims=" %%S in (rejects.txt) do ECHO del "%%S"
BUT
I really strongly recommend that you try this out on a copy of your images, before you find out you have lost irreplaceable pictures

We've all done it -- be aware that a batch file delete does not go into the recycle bin (unless you have downloaded and installed an extra piece of software), they are just gone

Actually, my example does not really delete, it prints to the screen the delete instructions that it would have executed, when you are sure all is well, then remove the ECHO from the instruction, cross your fingers and fire it off
Hey thanks for that I'm GOING to try that out.

However you did mention that VBA Macro, I would like to gain as much experience with VBA as possible for my own personal benefit, so is there a chance that somebody can help me make this function through that? And just to be sure would Excel's VBA be sufficient for this operation or would I need VB.net or anything else?

Thanks again Quote from: gpl on July 13, 2010, 04:25:55 PM

yes, it is possible, the whizzy answer would be to have a vba macro that goes out and deletes the files for you

This assumes that rows 1 to 4 of column A in Sheet 1 contain path and filenames e.g.

Code: [Select]T:\tempvu\Test-2-01.qry
T:\tempvu\Test-2-02.qry
T:\tempvu\Test-2-03.qry
T:\tempvu\Test-2-04.qry

You can delete the Rem and MsgBox lines when you are happy that it runs OK

Code: [Select]Sub delfile()
        Sheets("SHEET1").Select
        Col = "A"
        StartRow = 1
          MAXROW = 4
        For Row = StartRow To MaxRow
                Range(Col & Row).Select
                MsgBox (Selection.Value)
                Rem Kill (Selection.Value)
        Next
End Sub

Thank you Salmon Trout that does work however there is still something I need help with.

On my original post I was a bit unclear on the information that is contained in the Excel Spreadsheet. Since these files are moving to different locations/harddrives very often, their file path/name is not contained in the spreadsheet. What is contained is a different number for each image called its "exposure #".  So under the "Exp" column I have a list of all of these various numbers.

Similarly, the files themselves, which are in .tif format, also have that same number in their filename. Except for they also have other unassociated numbers in their filename.

For example for exposure # 57, the filename would be something like "ABC2JL4_04007_00057_RGB.tif" where the 00057 is the exposure number.

So now what would be awesome is if Excel could take the number from the Exp column, and use it to locate its associated .tif file(after say I specified a directory somehow), and then I could just delete that file using the code that you suggested.

Am very appreciative of any willing to help!!!

 

  Quote from: Salmon Trout on July 14, 2010, 11:13:50 AM

You can delete the Rem and MsgBox lines when you are happy that it runs OK


That was carelessly written.

Correction:

You can uncomment the Kill line and comment out the MsgBox line when you are happy that it runs OK



Discussion

No Comment Found

Related InterviewSolutions