asked on

VBA - Receiving error when trying to kill a file

I have a function that is supposed to delete any file but the 5 newest files in a folder. All of the files are named:

yyymmddhhss.bak

When I run the code, I receive a Run-time error '75'

In debug.print, the path looks perfect. This is an example: F:\QBackup\20160802105355.BAK

This is the correct file

This is the code:

Function KillOldQB()

Dim fso As Object
Dim fcount As Object
Dim collection As New collection
Dim obj As Variant
Dim i As Long

Set fso = CreateObject("Scripting.FileSystemObject")
'add each file to a collection
For Each fcount In fso.GetFolder("F:\Qbackup\").Files

collection.Add fcount

Next fcount

'sort the collection descending using the CreatedDate
Set collection = SortCollectionDesc(collection)

'kill items from index 6 onwards
For i = 6 To collection.Count

FiletoKill = collection(i)
Debug.Print i
Debug.Print collection(i)
Kill (collection(i))
Next i

End Function

Gustav Brock

You probably need the name only:

Kill collection(i).Name

/gustav

Rick Rudolph

ASKER

This did not work.

These 2 lines:

Debug.Print collection(i)
Debug.Print collection(1).Name

Show this in the immediate Window

F:\QBackup\20160802105355.BAK
20160802111852.bak

Gustav Brock

Well, yes, you would need the full path.
But then, why not just:

collection(i).Delete

/gustav

Rick Rudolph

ASKER

This generates a different error...Permission denied

Gustav Brock

OK. If you are not granted delete rights, or the file is in use, you can't delete it.
So that has to be solved before you can proceed.

/gustav

Rick Rudolph

ASKER

I do have delete rights, and I can delete it manually

Gustav Brock

Perhaps when added to your collection, this puts a lock on the file.

Try this:

For Each fcount In fso.GetFolder("F:\Qbackup\").Files

If fcount.Name = "SomeFileName" Then
' a test file of yours that can be deleted.
fcount.Delete
End If

Next fcount

/gustav

Rick Rudolph

ASKER

I don't understand where to insert this code, and am not sure what "SomeFileName" is. After the all the advice on this, this is what I currently have.

Function KillOldQB()

Dim fso As Object
Dim fcount As Object
Dim collection As New collection
Dim obj As Variant
Dim i As Long

Set fso = CreateObject("Scripting.FileSystemObject")
'add each file to a collection
For Each fcount In fso.GetFolder("F:\Qbackup\").Files

collection.Add fcount

Next fcount

'sort the collection descending using the CreatedDate
Set collection = SortCollectionDesc(collection)

'kill items from index 6 onwards
For i = 6 To collection.Count

FiletoKill = collection(i)
Debug.Print i
Debug.Print collection(i)
Debug.Print collection(1).Name
collection(i).Delete
Next i

End Function

This code generates a permission denied.

This is the results of the debug.prints

6
F:\QBackup\20160802105355.BAK
20160802111852.bak

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rick Rudolph

ASKER

Thanks for your help. Below is what ultimately worked:

Function KillOldQB()

Dim fso As Object
Dim fcount As Object
Dim collection As New collection
Dim obj As Variant
Dim i As Long
Dim sfile As String
Set fso = CreateObject("Scripting.FileSystemObject")
'add each file to a collection
For Each fcount In fso.GetFolder("\\wayne-dc01\data\Qbackup\").Files

collection.Add fcount

Next fcount

'sort the collection descending using the CreatedDate

Set collection = SortCollectionDesc(collection)

'kill items from index 6 onwards
For i = 6 To collection.Count
sfile = collection(i)

fso.deletefile collection(i), True

Next i

End Function