Delete Windows files with Shell command (1 Viewer)

[allen675]

This works in windows command line as I have tried it:

ForFiles /p "C:\Users\David\Desktop\New Leads\ContactProofs" /s /d -30 /c "cmd /c del @file"

I thought I might be able to run it as a shell command within VBA but with CurrentProject.Path like this:

Shell ForFiles /p & Application.CurrentProject.Path & "\New Leads\ContactProofs" /s /d -30 /c "cmd /c del @file"

I have played around with it but keep getting syntax errors and I cant work it out!

Thanks in advance

 

[strive4peace]

first thought is to either:
1. create a batch file and run it
or
2. loop through files and compare each to the mask to see if it needs to be deleted
?

hopefully this code can give you some ideas

Function CreateBatchFile(sMessage As String) As String
'160711 strive4peace
'this code creates a batch file with a message in the current database directory.
'return path and filename if successfully written

   On Error GoTo Proc_Err
   CreateBatchFile =  ""

   Dim sPathFile As String _
      ,iFileNumber As Integer

   sPathFile = CurrentProject.Path &  "\SayMsg.Bat"

   'delete the file that is already there
   If Len(Dir(sPathFile)) > 0 Then
      Kill sPathFile
      DoEvents
   End If

   'get a handle
   iFileNumber = FreeFile

   'close file handle if it is open
   'ignore any error from trying to close it if it is not
   On Error Resume Next
   Close #iFileNumber
   On Error GoTo Proc_Err

   'open file for output
   Open sPathFile For Output As #iFileNumber

   'write something
   Print #iFileNumber, "@ECHO ON"
   Print #iFileNumber, "@ECHO " & sMessage
   Print #iFileNumber, "@Pause"

   CreateBatchFile = sPathFile
   'MsgBox "Done Creating " & sPathFile, , "Done"

Proc_Exit:
   On Error Resume Next
   'close the file
   Close #iFileNumber

   Exit Function

'ERROR HANDLER
Proc_Err:
   MsgBox Err.Description _
     ,, "ERROR " & Err.Number _
     &  "   CreateBatchFile"

   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume
End Function

'here is code to launch the procedure to create a batch file with a message and then run it:
Sub runCreateBatchFile()
'160711 strive4peace
'run a batch file

   On Error GoTo Proc_Err
   Dim sMessage As String _
      ,sPathFile As String _
      ,sCommand As String

   sMessage =  "This is a test"

   sPathFile = CreateBatchFile(sMessage)

   If Len(sPathFile) > 0 Then
      'to run without the user seeing what is going on :
'      Environ$("COMSPEC") is pathfile to cmd.exe
'      sCommand = Environ$("COMSPEC") & " /c " & sPathFile ' & " &&pause"
      'run and pause until user presses a key
      sCommand = sPathFile
      Shell sCommand,vbNormalFocus
   End If

Proc_Exit:
   On Error Resume Next
   Exit Sub

'ERROR HANDLER
Proc_Err:
   MsgBox Err.Description _
     ,, "ERROR " & Err.Number _
     &  "   runCreateBatchFile"

   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume
End Sub

 

[theDBguy]

Just curious what that command does. Does it delete all the files in the folder and also all the files in its subfolders but leaving the subfolders intact?

Have you considered simply using VBA to do the same thing?

 

[The_Doc_Man]

The syntax errors are probably because you should build the entire line first as a separate action and then shell it as a completed string.

That's a guess but at least a semi-educated one.

 

[Isaac]

Seems a bit more direct to just use VBA Kill

 

[The_Doc_Man]

@Isaac - if you are doing them one at a time, I would agree with you. Just develop the fully qualified file spec or a viable relative file spec and Kill the file. But there is the ability to erase multiple files in multiple directory levels using a single shell command. There, the command shell has you beat.

 

[arnelgp]

to delete all files:

Const files as string = "C:\Users\David\Desktop\New Leads\ContactProofs\*.*"
With CreateObject("Scripting.FileSystemObject")
    .DeleteFile files, True
End With

 

[allen675]

Sorry probably should of explained what I'm trying to achieve! My database allows the end user to attach (copy & past) proofs of attempted client contact to the database (folder under the database). This proof isn't required beyond 30 days, so rather than files building up I wanted an effective way to empty folder of files older than 30 days

 

[arnelgp]

Const path As String = "C:\Users\David\Desktop\New Leads\ContactProofs"
'for test purpose
'Dim path As String
'path = "c:\ArnelTest"
Dim folder, file
With CreateObject("Scripting.FileSystemObject")
    Set folder = .GetFolder(path)
    For Each file In folder.files
        With file
            If DateDiff("d", .DateLastModified, Date) > 30 Then
                .Delete
            End If
        End With
    Next
End With

 

[allen675]

Apologies for the delay have been away for a few days with the kids. I shall have a look through all suggestions today and update on my progress. Thank you all for your help so far.