Other How To Delete Multiple Files In VBA Microsoft Access 2007

[Mahares]

Here is my code in B4A which works fine. I like to do the same thing, which is delete multiple files inside a C drive subfolder using Microsoft Access where the files start with a given string and end with .csv. I need some help using VBA from the Access knowledgeable members.

Sub Globals
    Private MyExport As String = File.dirrootexternal & "/" & "test"
    Private TenderLU As String = "XYZ"
End Sub

Sub Activity_Create(FirstTime As Boolean)
    For Each f As String In File.ListFiles(MyExport)
        If f.StartsWith(TenderLU & "_DAILY") And f.EndsWith(".csv") Then
            File.Delete(MyExport,f)
        End If
    Next
End Sub

Thank you

 

[walt61]

Hi @Mahares , here you go (EDIT: I have tested it with Excel 2013 but don't expect issues with 2007 - holler if there would be a snag):

Function DeleteDailyCsvFiles(ByVal theFolder As String, ByVal prefix As String, ByVal ignoreErrors As Boolean) As Long

' Note: does not check sub-folders !
' Calling sequence:
' dim numFilesDeleted as long
' numFilesDeleted = DeleteDailyCsvFiles("C:\myfolder", "XYZ_DAILY", True)

Dim oneFile As Variant
Dim fldr As String
Dim lcasePrefix As String

If InStrRev(theFolder, "\") = Len(theFolder) Then
   fldr = theFolder
Else
   fldr = theFolder & "\"
End If

DeleteDailyCsvFiles = 0
lcasePrefix = LCase(prefix)
oneFile = Dir(fldr & "*.csv")

If ignoreErrors Then On Error Resume Next

While (oneFile <> "")
   If InStr(LCase(oneFile), lcasePrefix) = 1 Then
      Kill fldr & oneFile
      DeleteDailyCsvFiles = DeleteDailyCsvFiles + 1
   End If
   oneFile = Dir
Wend

If ignoreErrors Then On Error GoTo 0 ' Reset[/INDENT]

End Function