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
Rick RudolphAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
It's the For-Each loop you have:

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

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

Next fcount

It will, of course, only delete (if possible) this single file. If it works, while deleting using the collection doesn't, you cannot use a collection as you do.
If it doesn't, either the process misses rights for deletion or something else is holding the file, and this would have to be solved before you can proceed.

/gustav
0
 
Gustav BrockCIOCommented:
You probably need the name only:

    Kill collection(i).Name

/gustav
0
 
Rick RudolphAuthor Commented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

    collection(i).Delete

/gustav
0
 
Rick RudolphAuthor Commented:
This generates a different error...Permission denied
0
 
Gustav BrockCIOCommented:
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
0
 
Rick RudolphAuthor Commented:
I do have delete rights, and I can delete it manually
0
 
Gustav BrockCIOCommented:
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
0
 
Rick RudolphAuthor Commented:
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
0
 
Rick RudolphAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.