Error 70 Permission Denied trying to kill a workbook

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,363
Office Version
  1. 2016
Platform
  1. Windows
I'm having some real issues trying to kill a closed workbook - my routine has a main workbook and what I'm trying to achieve is the following;

1) Get the main workbook name and store it for use later
2) Save the current (main) workbook as a temporary file
3) Kill the original main workbook so I can open another (updated) workbook and save it as the original main file name

This is what I have so far:

VBA Code:
SavedFileName = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.SaveAs filename:=ThisWorkbook.Path & "\Temp.xlsm"
Application.EnableEvents = True
SetAttr ThisWorkbook.Path & "\Temp.xlsm", vbHidden
SetAttr ThisWorkbook.Path & "\" & SavedFileName, vbNormal
Kill ThisWorkbook.Path & "\" & SavedFileName
Application.ScreenUpdating = True
DoEvents

The workbooks are stored in a network folder and there are NO permission issues as the 'temp' file is deleted without an issue in another routine later. I am getting a persistent 'Error 70 Permission Denied' on the following line:

VBA Code:
Kill ThisWorkbook.Path & "\" & SavedFileName

I have tried various things to no avail - can anyone spot where the issue is?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
the first thing that comes to mind, which also makes perfect sense, is the fact that you are trying to issue a statement which has a purpose of deleting an actual file. And the file you are trying to delete is still open at the time you are requesting the KILL statement to be executed. This same error would be thrown by windows itself if you pressed the DELETE button on a file in windows explorer, but had the file open when you attempted it. Here's the MS doc ref on the statement: Kill statement (VBA)
 
Upvote 0

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,363
Office Version
  1. 2016
Platform
  1. Windows
That does make sense - I thought, (probably incorrectly), that saving the original file as something else and leaving it open would in effect close the original file but I guess not. So can anyone show me how to ensure that the original file is actually closed so I can delete it?
 
Upvote 0

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
I've never used SetAttr(), but are you sure that these 2 lines aren't causing the problem?
Code:
SetAttr ThisWorkbook.Path & "\Temp.xlsm", vbHidden
SetAttr ThisWorkbook.Path & "\" & SavedFileName, vbNormal
:if that 2nd code line is indeed what's causing the problem, then try to delete it and see if it works. I have a suspicion it will.
 
Upvote 0

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,363
Office Version
  1. 2016
Platform
  1. Windows
The first line causes no issues and never has - I have tried simply having the following but this gives the same error:

VBA Code:
Kill ThisWorkbook.Path & "\" & SavedFileName
 
Upvote 0

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
this worked perfectly for me:
Code:
Function t()
Dim savedFileName As String
savedFileName = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Temp.xlsm"
Application.EnableEvents = True
'SetAttr ThisWorkbook.Path & "\Temp.xlsm", vbHidden
'SetAttr ThisWorkbook.Path & "\" & SavedFileName, vbNormal
Kill ThisWorkbook.Path & "\" & savedFileName
Application.ScreenUpdating = True
DoEvents

End Function
as I suspected, the SetAttr() function is the problem. Why are you even including it?
 
Upvote 0

juddaaaa

Board Regular
Joined
Jan 4, 2020
Messages
208
Office Version
  1. 365
Platform
  1. Windows
Correct me if I'm wrong, but as well as trying to delete an open file, are you not trying to delete the file that is currently running the code?
 
Upvote 0

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
Correct me if I'm wrong, but as well as trying to delete an open file, are you not trying to delete the file that is currently running the code?
that's an irrelevant point because the file is being saved as another file before the kill statement is executed.
 
Upvote 0

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,363
Office Version
  1. 2016
Platform
  1. Windows
Whilst doing some research on this issue I read a post on another site which said you had to use SetAttr and vbNormal so I tried that.

I'm not trying to delete the current file as the following saves it as a temporary file leaving the original as it was, (in theory):

VBA Code:
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Temp.xlsm"
 
Upvote 0

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,363
Office Version
  1. 2016
Platform
  1. Windows
I'll try again Adam with the SetAttr commented out to see what happens....I'll report back
 
Upvote 0

Forum statistics

Threads
1,186,745
Messages
5,959,515
Members
438,429
Latest member
Ekana99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top