Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 04, 2016, 12:05:09 AM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Delete files listed in excel sheet  (Read 3710 times)

KoenFugro

  • Participant
  • Joined in 2008
  • *
  • Posts: 26
    • View Profile
    • Donate to Member
Delete files listed in excel sheet
« on: February 03, 2009, 07:14:40 AM »
Hi there people on this forum,

We recently discovererd a huge amount of duplicate files in one of the projects we do.
This files are spreaded everywhere, so deleting them by hand is really a job I dont wan't to do hehe!

I have a file listing in excel of all the duplicate files.. there is one column in excel, with the complete path and filename for example s:\ebn scanning\sf\processed\ebn000011111.pdf

All the files listed in excel must be deleted from the hard drive.
I think there must be a easy way to do that with AHK?

Thanks in advance people!



moonwatcher

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 8
    • View Profile
    • Donate to Member
Re: Delete files listed in excel sheet
« Reply #1 on: February 03, 2009, 07:32:58 AM »
Let excel do the work for you:

(example macro)


Option Explicit

Sub deleteFiles()
    Dim row, col As Integer
   
    row = 1 'starting row
    col = 1 'column with filenames
   
    While Not IsEmpty(Cells(row, col))
        If Dir(Cells(row, col)) <> "" Then
            Kill (Cells(row, col))
            Cells(row, col + 1) = "deleted"
        Else
            Cells(row, col + 1) = "not found"
        End If
        row = row + 1
    Wend
   
End Sub


This macro will get the filename from the first column in the active sheet, starting with the first row until the first blank cell is reached. A status is written into the 2nd column.
« Last Edit: February 03, 2009, 07:51:04 AM by moonwatcher »

KoenFugro

  • Participant
  • Joined in 2008
  • *
  • Posts: 26
    • View Profile
    • Donate to Member
Re: Delete files listed in excel sheet
« Reply #2 on: February 03, 2009, 07:38:51 AM »
Is this an AHK script? Or is this a VBA script?
I am not good in programming, so maybe a stupid question haha..

Thank for the really quick reaction!

moonwatcher

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 8
    • View Profile
    • Donate to Member
Re: Delete files listed in excel sheet
« Reply #3 on: February 03, 2009, 07:49:49 AM »
There are no stupid questions, only stupid answers ;-)

It is a VBA macro:

1) open your excel worksheet
2) press ALT+F11 to start the VBA editor
3) Insert -> Module
4) Paste the code into the editor
5) Alter the code to fit your needs (e.g. starting row and/or column number)
6) press ALT+F11 to switch back to excel
7) press ALT+F8 to run a macro and select deleteFiles

(you can speedup execution if you place Application.Calculate = false before the While-Loop and a corresponding Application.Calculate = true before the end-sub command; this will prevent excel from repainting the worksheet everytime a file has been deleted)
« Last Edit: February 03, 2009, 08:08:45 AM by moonwatcher »

KoenFugro

  • Participant
  • Joined in 2008
  • *
  • Posts: 26
    • View Profile
    • Donate to Member
Re: Delete files listed in excel sheet
« Reply #4 on: February 03, 2009, 08:50:44 AM »
Thanks alot!
I tried it and it worked for me!


 ;D