Solved

Delete folders in Windows XP from Excel using VBA

Posted on 2013-06-17
17
227 Views
Last Modified: 2013-06-20
Dear Experts:

I wonder whether I am able to delete folders using an Excel macro?

I got a parent folder named C:\parentfolder...

Below this parent folder  are many subfolders and sub-subfolders etc.

Many of these folders (they are always located on the lowest level) are called 'JPEG' and they all! need to be deleted.

Is this possible.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
  • 10
  • 4
  • 3
17 Comments
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Option Explicit

Function DeleteFolders(ByVal strDir As String, ByVal strFolderName As String)

    Dim oFS As Object
    Dim oDir, oSub

    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oDir = oFS.GetFolder(strDir)

    For Each oSub In oDir.SubFolders
        If oSub.Path = strDir & "\" & strFolderName Then
            oFS.DeleteFolder oSub
        End If
    Next oSub

    For Each oSub In oDir.SubFolders
        Call DeleteFolders(oSub.Path)
    Next oSub
    
End Function

Open in new window


Call it like this:
Call DeleteFolders("C:\ParentFolder\", "JPEG")

Open in new window

0
 
LVL 80

Assisted Solution

by:byundt
byundt earned 100 total points
Comment Utility
The preceding macro is case sensitive, and won't delete a folder named "jpeg"

This code is not case sensitive, and also lets you choose the parent folder using a file browser.
Dim fso As Object

Sub RmDirJPEG()
'Removes all folders and subfolders nuamed JPEG
Dim fldr As Object
Dim sPath As String
sPath = Application.GetOpenFilename("All files (*.*),*.*", Title:="Select any file in parent folder")
If sPath = "False" Then Exit Sub
sPath = Left(sPath, InStrRev(sPath, Application.PathSeparator) - 1)
Set fso = CreateObject("Scripting.FileSystemObject")

Set fldr = fso.GetFolder(sPath)
DeleteFolder fldr
Set fldr = Nothing
Set fso = Nothing
End Sub

Sub DeleteFolder(fldr As Object)
Dim SubFldr As Object
Dim sPath As String
sPath = fldr.Path
For Each SubFldr In fldr.SubFolders
    DeleteFolder SubFldr
Next
If UCase(fldr.Name) = "JPEG" Then
    fso.DeleteFolder fldr
End If
End Sub

Open in new window

Brad
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
To deal with the issues mentioned by Brad I would use the following variation of my original code:
Option Explicit

Function DeleteFolders(ByVal strFolderName As String, Optional ByVal strDir As String)

    Dim oFS As Object
    Dim oDir, oSub
    Dim strPath As String

    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oDir = oFS.GetFolder(strDir)

    If Len(strDir) < 1 Then
        strPath = SelectFolder("C:\")
    End If

    For Each oSub In oDir.SubFolders
        If UCase(oSub.Path) = UCase(strDir) & "\" & UCase(strFolderName) Then
            oFS.DeleteFolder oSub
        End If
    Next oSub

    For Each oSub In oDir.SubFolders
        Call DeleteFolders(oSub.Path)
    Next oSub

End Function

Function SelectFolder(Optional strStartDir As String) As String
    Dim SA As Object, f As Object
    Set SA = CreateObject("Shell.Application")
    If Len(strStartDir) > 1 Then
        Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
    Else
        Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64)
    End If
    If (Not f Is Nothing) Then
        SelectFolder = f.Items.Item.Path
    End If
    Set f = Nothing
    Set SA = Nothing
End Function

Open in new window


To call the function use either this (where you pass the parent folder as a parameter):
Call DeleteFolders("JPEG", "C:\ParentFolder\")

Open in new window

Or this (where the user selects the parent folder using a folder picker):
Call DeleteFolders("JPEG")

Open in new window

0
 

Author Comment

by:AndreasHermle
Comment Utility
As always, I am very impressed at the fantastic support a user gets if he turns to EE for help.

I will do some testing and then let you know.

Thank you very much.

Regards, andreas
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Brad,

again, thank you very much for your great help. I am afraid to tell you that the code throws an error message on line 26: Runtime Error 70 Permission Denied.

Any idea why?

Regards, Andreas
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Are the files in question Read-only or is the folder marked Read-only?

As a workaround for the above possibilities, you might consider adding the optional second parameter to that statement:
    fso.DeleteFolder fldr, True        'The True means to delete the folder even if read-only
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Macro Shadow:

I am calling the function as follows:

Sub CallingFunction()
Call DeleteFolders("JPEG")
End Sub

I am afraid to tell you that I am receiving an error message on line 10 of your revised code:
Invalid Procedure call or argument, Runtime Error 5

Any idea why?

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Brad,

thank you very much for your swift support.

ok, this must be the cause:

The check box next to the 'Read Only' Option in the Properties menu is checked and grayed out, hence it seems I don't have permission to change it.

Strangely, I can delete the folder by pressing Delete.

Anyhow, even by substituting  'fso.DeleteFolder fldr, True' the error messages keeps appearing.

Regards, Andreas
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
My bad. I will be posting a working version shortly.
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi MacroShadow,

take your time, there is no need to rush. But as you can see from the thread, I am obviously having problems to have folders deleted due to read-only protection, although I can delete folders manually

Anyhow, I give the codes a try on my private computer.

Regards, Andreas
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 400 total points
Comment Utility
Not optimized (works pretty slow for directories that contain many sub-folders) will work on optimization later.
Function DeleteFolders(ByVal strFolderName As String, Optional ByVal strDir As String)

    Dim oFS As Object
    Dim oDir, oSub
    Dim strPath As String
On Error Resume Next
    Set oFS = CreateObject("Scripting.FileSystemObject")

    If Len(strDir) = 0 Then
        strPath = SelectFolder("C:\")
    Else
        strPath = strDir
    End If
    
    If Len(strPath) = 0 Then Exit Function
    
    Set oDir = oFS.GetFolder(strPath)

    For Each oSub In oDir.SubFolders
        If UCase(oSub.Path) = UCase(strPath) & "\" & UCase(strFolderName) Then
            oFS.DeleteFolder oSub
        End If
    Next oSub

    For Each oSub In oDir.SubFolders
        Call DeleteFolders(strFolderName, oSub.Path)
    Next oSub

End Function

Function SelectFolder(Optional strStartDir As String) As String
    Dim SA As Object, f As Object
    Set SA = CreateObject("Shell.Application")
    If Len(strStartDir) > 1 Then
        Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64, strStartDir)
    Else
        Set f = SA.BrowseForFolder(0, "Choose a folder", 16 + 32 + 64)
    End If
    If (Not f Is Nothing) Then
        SelectFolder = f.Items.Item.Path
    End If
    Set f = Nothing
    Set SA = Nothing
End Function

Open in new window

0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi MacroShadow,

I am impressed. Works like a charm. Great. Saves me hours of time. I do not find it slow at all. Thank you very much for your great coding expertise

Brad:
Since MacroShadow's code is running fine, the runtime error that is thrown up must have some other cause, BUT I have to stress, I have received so many good working codes from you so far (I'd say at least 50 or 60) ,  this code is the first one where an error message occurred. And I am sure you will find out, why this is so!

Regards, Andreas
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Andreas,
I'm not able to reproduce your problem with Windows 7, so maybe it's a difference with Windows XP, or perhaps the folder in question belongs to an Administrative account.

The only difference I can see between MacroShadow's successful code and my unsuccessful one is this statement at the beginning:
On Error Resume Next

Open in new window

Does the folder that causes a problem in my code get deleted by MacroShadow's code? I'm thinking that it might not.

Brad
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Brad,

I re-tested it. MacroShadow's code works fine on any folder I tried. Yes, this is really strange. I will give it a try on my personal computer and then let you know.

Regards, Andreas


Again, thank you very much for your great support.
0
 

Author Comment

by:AndreasHermle
Comment Utility
Brad:

I was gonna test it yesterday, but did not find time. It'll be today and then I will get back to you.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
Comment Utility
Hi Brad,

this is really weird. Your macro works on my home computer but not at my working place. MacroShadow's code work on both, hence I guess, I will now proceed to the awarding of points and award more points to MacroShadow's code. I am sure you will understand this.

Thank you very much to both of you.

Regards, Andreas
0
 

Author Closing Comment

by:AndreasHermle
Comment Utility
Dear both,

thank you very much for your professional support. I really appreciate your work.

Regards, Andreas
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now