delete folder

[Janelle]

I'm trying to delete several folders with vba code. At
any given time, the folders may or may not contain files
or subfolders. The files or subfolders may or may not be
read only. But none of them should be in use. The folder
that I'm trying to delete is not readonly, and I have full
security permissions on it. No one else is using these
folders or files.

I tried writing a sub using rmdir:

Public Sub KillDir(strPathName As String)
If Right(strPathName, 1) <> "\" Then
strPathName = strPathName & "\"
End If
Kill strPathName & "*.*"
RmDir strPathName
End Sub

Besides the fact that it needs to allow for the
possibility that the folder might not contain any files,
it can't handle subfolders, and it's giving me an error on
rmdir: "Run-Time error '75': Path/File access error". I
also tried giving it the path without the final "\", but
that gave the same error. I also tried
inserting "chdir "C:\" just before rmdir in case Excel had
it set as the current directory, but that didn't help
either.

Then I tried a different code (I have a reference set to
Microsoft Scripting Runtime):

Public Sub KillFolder(strPathName As String)
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.DeleteFolder strPathName, force:=True
End Sub

That one gives me "Run-time error '76': Path not found" if
strPathName ends with a "\" and "Run-time error '70':
Permission denied" if it doesn't.

I tried writing it a little differently:

Public Sub KillFolder(strPathName As String)
Dim FSO As FileSystemObject
Set FSO = New FileSystemObject
FSO.DeleteFolder strPathName, force:=True
End Sub

but that worked exactly the same.

I also tried manually deleting the folder with a right
click (which usually works), but until I close Excel, I
get an error there too: "Error Deleting File or Folder
Cannot remove folder [folder name]: There has been a
sharing violation. The source or destination file may be
in use."

All I want to do is delete the !@#$%^&* folders and
files! What am I doing wrong??? How can I make this
work???

 

[Jim Rech]

I tried your code like this:

Sub a()
KillFolder "c:\aa"
End Sub

Public Sub KillFolder(strPathName As String)
Dim FSO As FileSystemObject
Set FSO = New FileSystemObject
FSO.DeleteFolder strPathName, force:=True
End Sub

and it worked perfectly. Folder "aa" had files in it and subfolders with
files.

 

[Kevin Beckham]

Try this
(used this approach because Dir function is not re-entrant)

Sub RecursiveRmDir(folderName As String)
Dim fList As String
Dim fName As String

'make sure no trailing backslash
If Right(folderName, 1) = "\" Then folderName = Left
(folderName, Len(folderName) - 1)
'this will remove the files
Kill folderName & "\*.*"

fList = ""
fName = Dir(folderName & "\*.*", vbDirectory)
'build a list of sub-folders
Do While fName <> ""
If Left(fName, 1) <> "." Then fList = fList &
fName & "\"
fName = Dir()
Loop

Do While fList <> ""
fName = Left(fList, InStr(fList, "\") - 1)
RecursiveRmDir folderName & "\" & fName
fList = Mid(fList, InStr(fList, "\") + 1)
Loop
'this will remove the folder
RmDir folderName
End Sub


Kevin Beckham

-----Original Message-----
I'm trying to delete several folders with vba code. At
any given time, the folders may or may not contain files
or subfolders. The files or subfolders may or may not be
read only. But none of them should be in use. The folder
that I'm trying to delete is not readonly, and I have full
security permissions on it. No one else is using these
folders or files.

I tried writing a sub using rmdir:

Public Sub KillDir(strPathName As String)
If Right(strPathName, 1) <> "\" Then
strPathName = strPathName & "\"
End If
Kill strPathName & "*.*"
RmDir strPathName
End Sub

Besides the fact that it needs to allow for the
possibility that the folder might not contain any files,
it can't handle subfolders, and it's giving me an error on
rmdir: "Run-Time error '75': Path/File access error". I
also tried giving it the path without the final "\", but
that gave the same error. I also tried
inserting "chdir "C:\" just before rmdir in case Excel had
it set as the current directory, but that didn't help
either.

Then I tried a different code (I have a reference set to
Microsoft Scripting Runtime):

Public Sub KillFolder(strPathName As String)
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.DeleteFolder strPathName, force:=True
End Sub

That one gives me "Run-time error '76': Path not found" if
strPathName ends with a "\" and "Run-time error '70':
Permission denied" if it doesn't.

I tried writing it a little differently:

Public Sub KillFolder(strPathName As String)
Dim FSO As FileSystemObject
Set FSO = New FileSystemObject
FSO.DeleteFolder strPathName, force:=True
End Sub

but that worked exactly the same.

I also tried manually deleting the folder with a right
click (which usually works), but until I close Excel, I
get an error there too: "Error Deleting File or Folder
Cannot remove folder [folder name]: There has been a
sharing violation. The source or destination file may be
in use."

All I want to do is delete the !@#$%^&* folders and
files! What am I doing wrong??? How can I make this
work???
.

 

[Janelle]

Thanks for your testing and suggestions.

I did finally figure out the problem. The function I use
to make sure the folders exist in the first place uses the
Dir() function. And apparently, that function was
retaining possession (for lack of a better word) of the
folder that was tested last. So I tossed in a line at the
bottom of the function that points Dir() to something
different...something I know I won't need to delete... C:\

Now everything works great!