CODEWITHSUNDEEP
excelvbadelete-filepermission-denied
user7215723
user7215723

Reputation:

Permission sometimes denied to kill/delete file

I have an Excel program that relies on reading, appending, and creating text files.

Since starting this project, once every 10-100 times, I get a

"Run-time error '70': Permission denied"

error when trying to kill/delete a text file.

If I continue executing the code, it works.

I've done some research about the error code, but that hasn't been much help.

Earlier, I theorized the file doesn't get time to close, before Excel tries to kill it. I tried a function that waited for the file to be closed, so unless my code for that doesn't work, I don't think that's the problem.

Here's my code for closing and deleting files. Note, it's on the "Kill" command it stops. The "isFileOpen" and "dir" commands were to check if the error was due to 1) files not being closed yet, and 2) files not being deleted yet.

Close textfileorg
Close textfileNew
    'Erstatter gammelt reg med nytt reg
Do While isFileOpen(filepathorg): Loop
Kill filepathorg
Do While dir(filepathorg) <> "": Loop

Do While isFileOpen(filepath): Loop
Kill filepath
Do While dir(filepath) <> "": Loop

Name filepathNew As filepathorg

Here's my "is file open" check:

Function isFileOpen(ByRef filepath As String)

Dim textfile As Integer
textfile = FreeFile

On Error GoTo fileOpenErr

    'Forsøker å åpne fil
Open filepath For Random As textfile
Close textfile

    'Om ok, returner at fil ikke er åpen
isFileOpen = False

Exit Function

    'Ved error, returner at fil var åpen
fileOpenErr:
    isFileOpen = True

End Function

I suppose the problem could be avoided by using code along the lines of

failToKillFile:
    application.wait(time)
    resume

I feel that's just avoiding the problem. I would like to know why the error happens in the first place.

Edit: Just as I posted this, I got a similar error trying to copy a file, where the copy would have to replace a file. Press continue, and everything works.

Upvotes: 3

Views: 16437

Answers (4)

Kellog
Kellog

Reputation: 11

I’ve run into the same error and here’s what I think the problem is:

If you were to manually attempt to delete a file such as C:\test.txt, you’d get the following error message:

File Access Denied

You’ll need to provide administrator permission to delete this file

So when Excel VBA tries to delete a file in the root directory, I think it also gets an error message.

For me, at least, that seems to be the cause of the problem.

Upvotes: 0

Storax
Storax

Reputation: 12167

Change in your code the line Open filepath For Random As textfile to Open filepath For Input Lock Read As textfile

Complete code https://support.microsoft.com/en-us/help/291295/macro-code-to-check-whether-a-file-is-already-open <= Not longer avaliable, gone

As the link is gone

Function IsFileOpen(fileName As String)

Dim fileNum As Integer
Dim errNum As Integer

'Allow all errors to happen
On Error Resume Next
fileNum = FreeFile()

'Try to open and close the file for input.
'Errors mean the file is already open
Open fileName For Input Lock Read As #fileNum
Close fileNum

'Get the error number
errNum = Err

'Do not allow errors to happen
On Error GoTo 0

'Check the Error Number
Select Case errNum

    'errNum = 0 means no errors, therefore file closed
    Case 0
    IsFileOpen = False
 
    'errNum = 70 means the file is already open
    Case 70
    IsFileOpen = True

    'Something else went wrong
    Case Else
    IsFileOpen = errNum

End Select

End Function

Upvotes: 1

Tom Annable
Tom Annable

Reputation: 1

I had this very issue and nothing seemed to solve it until i moved the kill command into a separate subroutine and called it from the main routine...in my case:

Sub KillWB()
Dim UPath As String, WB As String
UPath = "C:\Users\" & Environ("username")
WB = UPath & "\Temp.xlsx"
Kill WB
End Sub

Upvotes: 0

etfa
etfa

Reputation: 189

i just experienced the same issue, with permissions (apparently) randomly denied but...

it happened to me for because an explorer window was opened on the folder i wanted to delete.

yes, just that.

Upvotes: 0

Related Questions