VBA Kill Command

[pouliotg]

I would like to use the Kill command and include all sub folders.

Kill = "D:\DATA\*.TXT"

In data I have over 100+ folders and I would like to have any Txt file zap as well.

What command would use to include all sub folders in D:\DATA.

Thanks

 

[Oorang]

Option Explicit
Sub Test()
    If KillSubFolders("C:\Test") Then
        MsgBox "Deleted subfolders"
        Else
        MsgBox "Did not delete subfolders"
        End If
End Sub
Function KillSubFolders(strFolderSpec As String, Optional blnForce As Boolean) As Boolean
    On Error GoTo Err_Hnd
    'strFolderSpec: Required.
        'The name of the folder to delete. The folderspec can contain wildcard
        'characters in the last path component.
    'blnForce: Optional.
        'Boolean value that is True if folders with the read-only attribute set
        'are to be deleted; False (default) if they are not.
    Dim fso As Scripting.FileSystemObject
    'Note: Needs a reference to Microsoft Scripting Runtime
    Set fso = New Scripting.FileSystemObject
    If fso.FolderExists(strFolderSpec) Then
        fso.DeleteFolder strFolderSpec, blnForce
        KillSubFolders = True
        End If
    Exit Function
Err_Hnd:
    KillSubFolders = False
    If Err.Number <> 76 Then
        MsgBox Err.Description, vbCritical, "Error: " & Err.Number
        End If
End Function

 

[Greg Truby]

You want to delete the folders? Or keep the folders and just clean out any *.txt files in these subfolders?

 

[Scott Huish]

I would suggest you backup your data in these folders before you try anything in this thread, because if it doesn't do what you want, then you are going to have a problem.

My question is are you just trying to kill text (*.txt) files in these folders or all the folders and files under D:\Data regardless of name?

 

[Oorang]

Excellent concerns, all. This should compensate for a few varieties of intent:

Option Explicit
Sub Test()
    If KillSubFolders("C:\Test", False, True, "*.txt") Then
        MsgBox "Deleted subfolders"
        Else
        MsgBox "Did not delete subfolders"
        End If
End Sub
Function KillSubFolders(strFolderSpec As String, blnDeleteFolder As Boolean, _
            Optional blnForce As Boolean, Optional strFileExtension As String = ".*") As Boolean
    On Error GoTo Err_Hnd
    'strFolderSpec: Required.
        'The name of the folder to delete. The folderspec can contain wildcard
        'characters in the last path component.
    'blnDeleteFolder: Required
        'Determine if function should remove the folder itself or only the files
        'within it.
    'blnForce: Optional.
        'Boolean value that is True if folders with the read-only attribute set
        'are to be deleted; False (default) if they are not.
    'strFileExtension: Optional
        'Specifies one specific file type to remove. Can only be used if
        'blnDeleteFolder is set to false
    'Note: Needs a reference to Microsoft Scripting Runtime
    Dim fso As Scripting.FileSystemObject
    Dim f As Scripting.File
    Dim fldr As Scripting.Folder
    If blnDeleteFolder And strFileExtension <> ".*" Then Err.Raise 513
    Set fso = New Scripting.FileSystemObject
    If fso.FolderExists(strFolderSpec) Then
        If blnDeleteFolder Then
            fso.DeleteFolder strFolderSpec, blnForce
            KillSubFolders = True
            Else
            Set fldr = fso.GetFolder(strFolderSpec)
            For Each f In fldr.Files
                If VBA.Mid$(f.Name, VBA.InStrRev(f.Name, ".")) Like strFileExtension Then
                    f.Delete (blnForce)
                    End If
            Next f
            KillSubFolders = True
            End If
        End If
    Exit Function
Err_Hnd:
    KillSubFolders = False
    If Err.Number <> 76 Then
        MsgBox Err.Description, vbCritical, "Error: " & Err.Number
        End If
End Function

 

[Scott Huish]

Perhaps:

Sub test()
Dim fpath As String, ftype As String, i
fpath = "D:\DATA"
ftype = "*.txt"
With Application.FileSearch
    .NewSearch
    .LookIn = fpath
    .SearchSubFolders = True
    .Filename = ftype
    If .Execute() > 0 Then
        For i = 1 To .FoundFiles.Count
            Kill .FoundFiles(i)
        Next i
    End If
End With
End Sub

 

[Oorang]

Yah but mine's faster

 

[kumarvelcbe]

Hi friend this is kumaravel. I need one favour. I have many excel file in single folder. By using Kill Command need to delete the file if A2 cell is empty. I have tried a lot. But i dont know how to assign the current work book name to one variable and how to pass that variable in kill command. Kindly suggest me. Thanks in advance.