[RESOLVED] How to delete folders in excel?

[frankwhite]

Hi guys

I was hoping someone could offer me some help please in excel, basically I have a folder which has over 1000 folders inside and I need to delete certain folders inside this folder but this will take me hours and hours.

What I have is in column A I have the folder names that I want deleting from the folder, is this possible if I add the path in there?

Thanks

[dmaruca]

You can use the filesystemobject. Here is a tutorial on it.

[JJJCR_FOX]

hi frankwhite, if your data in column a got a complete path plus the name of the folder to be deleted.. try this...

but you need to reference the "Microsoft Scripting Runtime" at VBA editor. Go to tools->references.. scroll down until you find the MS Runtime

Sub xx()
Dim filesys
Dim str As Variant
Dim i As Integer



Set filesys = CreateObject("Scripting.FileSystemObject")
For i = 1 To 2             'change the value up to the number of columns you want to delete
str = Range("a" & i).Value

If filesys.FolderExists(str) Then
   filesys.DeleteFolder str
   MsgBox "Folder deleted"
   Else
   MsgBox "Folder not found"
End If
Next

End Sub

[Siddharth Rout]

I would suggest the API way. It is much faster than FSO. And you don't need to set any kind of references...

Private Declare Function SHFileOperation Lib "shell32.dll" _
Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
Private Type SHFILEOPSTRUCT
    hWnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAborted As Boolean
    hNameMaps As Long
    sProgress As String
End Type
Private Const FO_DELETE = &H3
Private Const FOF_NOCONFIRMATION As Long = &H10
'~~> This will delete all folder paths stored in cell
'~~> A1 to A10 in Sheet1. Please amend it as per requirement
Private Sub DeleteFolder()
    Dim SHDirOp As SHFILEOPSTRUCT
    '~~> From Row 1 to 10
    For i = 1 To 10
        With SHDirOp
            .wFunc = FO_DELETE
            '~~> Take Folder path from Cell Value
            .pFrom = Sheets("Sheet1").Range("A" & i).Value
            '~~> Comment the below if you want a confirmation
            '~~> each time the folder needs to be deleted
            .fFlags = FOF_NOCONFIRMATION
        End With
        '~~> Perform delete operation
        SHFileOperation SHDirOp
        DoEvents
    Next i
End Sub

[frankwhite]

Hi guys

2 questions, 1 where does the path go in the coding? and 2 when I try to run the macro its empty - am I suppose to add private sub at the top?

Thanks

[Siddharth Rout]

If you are using my code then you don't need to enter the path in the code. Simply put the paths in the cells of Col A in Sheet 1

Click on Menu Tools->macros->macro and then select DeleteFolder and click run...

Remember these few things...

1) I have not done any error trapping so ensure that if there are say 10 folders that you want to delete then start putting them from A1 to A10 without any blank cell in between.

Change the "For i = 1 to 10" in the code accordingly...

2) Folders/Files once deleted cannot be recovered. If you want to delete the folder and files and send them to recycle bin then let me know...

[elleg]

I would suggest the API way. It is much faster than FSO. And you don't need to set any kind of references...

Private Declare Function SHFileOperation Lib "shell32.dll" _
Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Hey Koolsid,
I've been using VBA to do progressively more complex things for around 5 years now, and I can do a fair amount with it. I had a class in college in VB.NET, but my VBA knowledge is all from trial and error, copying and pasting from code I find in forums, etc., so keep that in mind when answering my question:
I've deleted files/folders in a completely different way than you proposed (I believe I used the 'kill' statement, which worked for my purposes, though I don't know how correct it really is to use it), but I wanted to try your method, as it seems like you actually know what you're doing in this area (unlike me). I've never had luck using API stuff before, it's really out of my realm of knowledge. So, I pasted your code into my module to try it out, and the line of code I quoted above immediately threw the error: 'Compile Error: Only comments may appear after End Sub, End Function, or End Property.', which has been my usual problem with trying out API stuff. Any ideas on this?

[dmaruca]

elleg, it sounds like you are pasting the declare statement below your other code. Declare's have to be at the top of a module. I usually put API's in their own module.

The difference between the Kill statement, the filesystemobject, and the api method are just library support (as I call it). Kill is directly supported and a part of the VBA language, so you should be able to expect it to work regardless of the version of windows or operating system. The filesystemobject is a vbscript library, so it required internet explorer to be installed. This may be a problem if you run your code on the Mac, but should be ok on Windows. The API method is windows only and may or may not work depending on the version of Windows you're using. The completely depends on the dll and API you are using, of course.

It's up to you as the developer to decide which is best given your time, platform, and experience. Good luck

Edit: VBScript isn't an IE only thing. http://en.wikipedia.org/wiki/VBScript

[elleg]

Duh... I feel stupid now. I didn't realize that every time I've tried an API function, I've most likely just pasted it wherever in a module I had open. I can't believe I overlooked putting a declare statement at the top... dee dee dee. Is there some place I can read up on API functions? I don't know how people just know about them... the only way I find out about them is when somebody posts the use of one in a forum somewhere.

Frankwhite, here's the procedure I use to delete folders:

Sub DeleteFolders()
    Dim i As Integer 'should be fine for 1000 folders
    Dim iNumToDelete as Integer 'should be fine for 1000 folders
    
    'put the paths of the folders you want to delete in column A
    'start in cell A1, and don't leave blank rows
    
    iNumToDelete = Application.WorksheetFunction.CountA(Range("A:A"))
    
    For i = 1 To iNumToDelete
        RmDir (Range("A" & i).Value)
        'if you want to delete files instead of folders,
        'comment out the line above starting with RmDir
        'and un-comment the line belowstarting with Kill
        'Kill (Range("A" & i).Value)
    Next i
    
End Sub

[Siddharth Rout]

Sorry Couldn't reply as was preparing this sample project...

I have spent a good amount of time working on this so I hope that you go thru it
Hope this helps....

Do let me know if you have any queries...

Edit:
For API's see this link

http://allapi.mentalis.org/apilist/apilist.php

[frankwhite]

Thanks Sid bhai

[Siddharth Rout]

Glad to help you bhai