1

I am trying to build a macro that will search in the /C:Users/"their username"/Downloads folder to delete any files that have the same saved name, but with different numbers at the end of the file names.

I was able to get the macro to delete a file with no issue, my question is how do I make this "loop" to be delete every file that has the same name in that folder until there are no more remaining?

(I am very new to VBA/macro building and have built this around research online and previous macros)

Example of file names in /Downloads that need deleted

Here is what I currently have:

Sub killxls()
' killxls Macro
'
' Macro used to delete all "Dock_Activity_*.xls" files
' in "C:\Users\("username")\Downloads\"
'
' Keyboard Shortcut: Ctrl+t
Dim Filename, Pathname As String
Dim Killfile As String
Dim UserName As String

    UserName = Environ("username")
    Pathname = "C:\Users\" & Environ$("username") & "\Downloads\"
    Filename = Dir(Pathname & "Dock_Activity_*.xls")
    Killfile = "C:\Users\" & Environ$("username") & "\Downloads\" & "Dock_Activity_*.xls"

Do While Killfile <> ""
    KillProperly "Pathname & Killfile"
    Killfile = Dir$(Pathname & "Dock_Activity_*.xls")
Loop


End Sub

Public Sub KillProperly(Killfile As String)

If Len(Dir$(Killfile)) > 0 Then
    SetAttr Killfile, vbNormal
    Kill Killfile
End If

End Sub

Thank you in advance for your help!

Teamothy
  • 2,000
  • 3
  • 16
  • 26
Colvin
  • 11
  • 4
  • After calling the `Dir` command the first time with the wildcard, each subsequent call to it with no argument will give you the next file. For deleting the files, it might be best to build an array using this technique and then process the array to delete the files. – braX Nov 21 '19 at 07:52
  • Also, dont use your `KillProperly` routine inside of the Dir Loop because it also uses the `Dir` function, which will mess things up, unless you read the filenames into an array first (which is one reason I suggested the Array method) - or change your routine to use a file system object to check for its existence instead of the `Dir` function. – braX Nov 21 '19 at 07:58
  • I want to point out that your `Filename` is a `Variant` and not a `String` as one might think. To `Dim` it correctly in one line use `Dim Var1 As VarType, Var2 As VarType, ...` – Nacorid Nov 21 '19 at 10:40

1 Answers1

0

After calling the Dir command the first time with the wildcard, each subsequent call to it with no argument will give you the next file.

And if you are getting the list from the Dir command, there is no need to check if it exists first, as you are doing with your KillProperly routine, so that goes away.

As per my comments above, something like this should work:

Sub killxls()
' killxls Macro
'
' Macro used to delete all "Dock_Activity_*.xls" files
' in "C:\Users\("username")\Downloads\"
'
' Keyboard Shortcut: Ctrl+t
  Dim Pathname As String
  Dim Killfile As String
  Dim FileCount As Long

  Pathname = "C:\Users\" & Environ("username") & "\Downloads\"

  KillFile = Dir(Pathname & "Dock_Activity_*.xls")
  Do While Killfile <> ""
    SetAttr Pathname & Killfile, vbNormal
    Kill Pathname & Killfile
    FileCount = FileCount + 1
    Killfile = Dir()
  Loop

  MsgBox FileCount & " file(s) have been deleted."

End Sub

I also added a confirmation at the end that is optional, as it is good form to let the user know when it completes and what or how many actions were completed. That can be removed if it is unwanted.

braX
  • 11,506
  • 5
  • 20
  • 33