VBA to delete all files in folder whose name begin with specific striing

srejja
srejja used Ask the Experts™
on
I need code that will delete all files within a folder that begin with a specific string, except for the most recent version.  

For instance in folder C:\ I will have the following files:
C:\Test_01292010.zip
C:\Test_01282010.zip
C:\Test_01272010.zip
C:\Test_01262010.zip
C:\KeepMe_01252010.zip
C:\SaveMe_01282010.xls

In this example  only files I would want anything beginning with "Test_" deleted except for the most recent version " C:\Test_01292010.zip".  Thus C:\Test_01282010.zip, C:\Test_01272010.zip, C:\Test_01262010.zip would be deleted and the rest would be kept.  How would I go about achieving this?

Thus far I've been using the code below to delete specific files.  

Public Sub KillProperly(Killfile As String)
On Error Resume Next
    If Len(Dir$(Killfile)) > 0 Then
        SetAttr Killfile, vbNormal
        Kill Killfile
    End If
End Sub

Now
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
See if you can adapt the code from this post:

http://excel-user.blogspot.com/2009/10/replace-value-on-several-excel-files.html

jppinto

Author

Commented:
No success with that one.  I am a mere apprentice VBA coder :(
This code will delete earlier versions of all files in the chosen directory IF the files contain
_mmddyyyy

I suggest you try it on a sample folder and ile set first

Cheers
Dave

Sub KillEm()
    Dim MyPath As String, objFile
    Dim objDic As Object
    Dim txtMarker

    Set objDic = CreateObject("scripting.dictionary")
    MyPath = "c:\"
    objFile = Dir(MyPath & "\*.*")
    Do While Len(objFile) <> 0
        txtMarker = InStr(objFile, "_")
        If txtMarker > 0 Then
            If objDic.exists(Left$(objFile, txtMarker - 1) & Right$(objFile, 4)) Then
                If ConDate(Mid$(objFile, txtMarker + 1, 8)) > ConDate(objDic(Left$(objFile, txtMarker - 1) & Right$(objFile, 4))) Then
                    Kill MyPath & "\" & Left$(objFile, txtMarker) & objDic(Left$(objFile, txtMarker - 1) & Right$(objFile, 4)) & Right$(objFile, 4)
                    objDic(Left$(objFile, txtMarker - 1) & Right$(objFile, 4)) = Mid$(objFile, txtMarker + 1, 8)

                Else
                    Kill MyPath & "\" & objFile
                End If
            Else
                objDic.Add Left$(objFile, txtMarker - 1) & Right$(objFile, 4), Mid$(objFile, txtMarker + 1, 8)
            End If
        End If
        objFile = Dir
    Loop
End Sub

Function ConDate(anyStr) As Date
    ConDate = DateSerial(Right$(anyStr, 4), Left$(anyStr, 2), Mid$(anyStr, 3, 2))
End Function

Open in new window

Author

Commented:
Thanks Brett.  I tried your code and received an error: Run-time error '13':  Type mismatch

When debugging this line of code is highlighted:
 ConDate = DateSerial(Right$(anyStr, 4), Left$(anyStr, 2), Mid$(anyStr, 3, 2))


Do your file all have the layout like

XXX_mmddyyyy

as per your sample data?

as long as the "_" precedes an eight character date (as per your sample data) then the code should work
Cheers

Dave

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial