"Kill" statement works in immediate pane, but not in module loop

[John_G]

This is one for the experts!

I have been tasked to clean up part of one of our network drives, which involves merging 5 directories into one, and deleting duplicate and unnecessary files.

I have put the whole drive directory into MS Access, and using queries and VBA I have identified and marked the files to be deleted.

All this works perfectly, and marks 16,800 / 54,000 files for deletion. Here is the procedure I am trying to use to do the deletions, using the VBA Kill statement:

Sub KillFiles()
  Dim Filename  As String
  Dim db As Database, rst As Recordset
  Dim SQL As String
  On Error GoTo ErrProc
  Set db = CurrentDb
  SQL = "Select * from [All MOSART Files with Directories] where MarkForDeletion and not filedeleted and " & _
      " left([directory Name],15) = 'R:\MOSART FINAL'"
  Set rst = db.OpenRecordset(SQL)
  While Not rst.EOF
    Filename = Chr(34) & rst![directory name] & "\" & rst![file name] & Chr(34)
    Debug.Print "Kill " & Filename
    Kill Filename
    rst.Edit
      rst!filedeleted = True
    rst.Update
    rst.MoveNext
  Wend
  rst.Close
  Set db = Nothing
  Exit Sub
ErrProc:
  Debug.Print "Error " & Err.Description & " when deleting " & vbCrLf & Filename
  rst.Close
  Set db = Nothing
End Sub

However, when I run it, the code fails on the first file, with a "File not found" error. I know the file name is correct, because the debug.print shows it correctly.

Here is where it gets odd: if in the immediate window I click on the line that debug.print has produced, (see bold below) and execute it, it works - the file is deleted.

kill "R:\MOSART FINAL JG\5555-106 Briefings & Conf\fsa25A.tmp" (this works in the immediate window)

A little test shows that the simplest of the simple:

Sub x()


Dim txtFilename As String
txtFilename = "R:\MOSART FINAL JG\5555-101 Budget\dir budget Qtr - 20 Jun 01.xls"
Kill txtFilename
End Sub

does work. I'm thinking it may be a timing issue, where I try the second Kill before the first one has completed. I put a Doevents after the Kill, but no luck.

Any ideas?

Thanks!

John

[rpeare]

Have you tried using filesystemobject commands instead? I'm not a huge fan of the kill command because filesystemobject is much more versatile

You'd have to set it up with

dim fs
set fs = createobject("scripting.filesystemojbect")

then to delete you'd use

fs.deletefile txtFileName

also you do not need to have the chr(34) around your filename definition

use the snippet below, you've already defined filename as a string so you do not need to put " marks around it, in essence you're issing the command

kill "filename"

instead of

kill filename

Filename = rst![directory name] & "\" & rst![file name]

[John_G]

Many thanks!!!

The quotes around the file name were not only not needed, they were wrong!

Once I took them off, all was well.


Thanks again

John