VBA Получение ошибки времени выполнения (5) при попытке удалить существующий файл

Непроверенный, написанный на мобильном телефоне. Приведенный ниже код не сильно отличается от вашего кода, но может делать то, что вам нужно.

Option Explicit

Private Sub Workbook_Open()

Dim folderPath as string
folderPath = dir$("R:\Groups\Finance\Ops Finance\Reporting\F18 Cost Analysis\Standard Costing\Std Cost Variances\Variance Master Back-Ups\", vbdirectory)

If Len(folderPath) = 0 then
Msgbox("Could not locate folder")
Exit sub
Elseif strcomp(right(folderPath, 1),"\", vbbinarycompare) <> 0 then ' this might be unnecessary, depends if dir() on vbdirectory returns \ at end or not, cannot remember or test'
folderPath = folderPath & "\"
End if

Dim filenames() as string
Redim filenames(1 to 2, 1 to 1000) ' 1000 = magic number, change if needed.'

Dim fileIndex as long

Dim filename as string
Filename = dir$(folderPath & "*")

Do until Len(filename) = 0

Fileindex = fileindex +1
Filename(1, fileindex) = folderPath & filename
Filenames(2, fileindex) = filedatetime(Filename(1, fileindex))
Filename = dir$()

Loop

Redim preserve filenames(1 to 2, 1 to fileindex)

ThisWorkbook.SaveCopyAs folderPath & _
VBA.Replace(ThisWorkbook.Name, ".xlsm", "_copy_" & _
VBA.Format$(Now, "m-d-yyyy hhmmss AM/PM") & ".xlsm")

Dim Oldest as Date
Dim OldestIndex as long

Oldest = filenames(2,1) ' Initialise with first value'

' Might be better to store below in dictionary -- or any key-value/associative structure. But should work nonetheless.'
For fileindex = lbound(filenames,2) to ubound(filenames,2)
If filenames(2, fileindex) < oldest then
Oldest = filenames(2, fileindex)
OldestIndex = fileindex
End if
Next fileindex

Dim fileIsOpen as Boolean

On error resume next
Open filenames(1, OldestIndex) For Input Lock Read As #1
fileIsOpen = err.number <> 0
On error goto 0
Close #1

If fileIsOpen then
msgbox("Attempted to delete file at:" & filenames(1, OldestIndex) & " but file may be open elsewhere or by another user.")
Exit sub
Else
' In theory, the file could go from not-in-use to in-use between the check above and the delete below. Might be better just to try to kill without checking but with on error resume, and then checking if file still exists or is open.'
Kill filenames(1, OldestIndex)
End if

End sub