Delete a file without initially declare a variable of its path-CodePudding

I need to delete a file without initially declare a variable of its path.
I tried this code, but I got

Automation error

    Dim wb1 As Workbook: Set wb1 = Workbooks.Open("D:\Users\Waleed\Desktop\Query1.XLS")
    wb1.Close SaveChanges:=False
    Kill (wb1.FullName)   ‘`Automation error` on this line

If I initially declared a variable of its path, the code works without problem

Dim wb1_Path As String: wb1_Path = wb1.FullName
Kill (wb1_Path)

CodePudding user response：

You cannot access a property of the workbook after it is closed. You can, however, change the workbooks ChangeFileAccess to xlReadOnly and delete the file while the workbook is open.

Sub CloseAndDestroy(wb As Workbook)
    Application.DisplayAlerts = False
    With wb
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
    Application.DisplayAlerts = True
End Sub