Lookup folder names in spread sheet and delete them.

[domainguru]

Hi,

I am a totally new when it comes to VBA. That said, I have a list of folder names in one my excel sheets. I would like the VBA/Macro to look up the folder name in my excel workbook and delete that folder and all the content within it.

For example: Under Games folder, I have Civilization, Rise of Nations, Age of Empires, and Red Alert.

My Excel workbook has only Civilization, Rise of Nations, and Red Alert. I would like the vba to delete the 3 folders because their folder names are listed in the Excel worksheet.

Folders in Question:

C:\Games
C:\Games\Rise of Nations
C:\Games\Civilization
C:\Games\Red Alert
C:\Games\Age of Empires

I have found a script on this site that lets me delete the folder name, but I would like it to look up the spreadsheet for a list of names and then proceed to delete all folders with those names.

Sub DeleteFolder()
Dim MyPath As String, fs As Object, RetVal As Variant

MyPath = "C:\Games\Civilization" 'change this to the folder path you want deleted

'Initialise the FileSystemObject so you can use the DeleteFolder Method
Set fs = CreateObject("Scripting.FileSystemObject")

'Warn user
RetVal = MsgBox("You are about to delete the following folder:- " & vbLf & _
vbLf & MyPath & vbLf & vbLf & _
"This cannot be undone and will delete all files and Sub-Folders" & _
vbLf & "Click Yes to proceed or No to abort.", vbExclamation + vbYesNo, _
"Warning: Delete Folder")

'Stop procedure if they change their mind
If RetVal = vbNo Then Exit Sub

'Delete folder. See DeleteFolder Method in VBA Help
On Error Resume Next
fs.DeleteFolder MyPath, True
If Err.Number <> 0 Then
MsgBox "The specified path " & MyPath & " could not be found!", vbCritical, "Path Doesnt Exist"
Exit Sub
End If
On Error GoTo 0

'Check that folder was deleted
If Len(Dir(MyPath, vbDirectory)) = 0 Then
MsgBox "The folder " & MyPath & " was successfully deleted!"
Else
MsgBox "There was a problem deleting folder " & MyPath
End If

End Sub