Excel 2007 and the Kill Statement....

Gary_E

Board Regular
Joined
Feb 18, 2002
Messages
108
Hey Folks :

A couple of years back, - I ran into the dreaded :
Run-Time Error '1004' : Copy method of Workseet class failed; Have to save\close\open a
temp file to skip problem
'http://support.microsoft.com/default.aspx?scid=kb;en-us;210684&Product=xlw

So I implemented the recommended work around and it's worked perfectly ever since.
The only additional step I added was @ the end to open the temp file and copy the tabs over to a new workbook, close the temp file & then delete it via the Kill statement. (so I don't leave trash on my pc)

Now I'm being upgraded to office 2007 - so I'm testing my code. The code works perfectly until I get the Kill statement where I get a "Permission denied (Error 70). (It's a Build 5 using XP, on a HP Compaq)

I've tried every trick in the book I know of to work around it, but no luck. I've manually reopen the temp file just prior the kill statement and it says it's "read only". To me, it looks like the vba is keeping the temp file in it's memory (even though it's closed). If I close the "new workbook", the kill statement works. I've searched the web and couldn't find anything that would me a clue on how to deal with this one- I'm @ a lost for ideas

Any and all help would be appreciated....


I've added some snips of the code so you can get an idea of what I've been doing....

*****************************************************************
Public ext_home_file As ThisWorkbook
Public anth_hold_file As String, first_tab_temp As String
Public anth_temp_file As Workbook
Public rnge_name_delete As Name

Set ext_home_file = ActiveWorkbook

''Create the dummy file \ Temp file
anth_hold_file = ActiveWorkbook.Path & "\DELETE_" & Format(Now, "hhmmss")
Sheets(anth_tab.Name).Copy
ActiveWorkbook.SaveAs anth_hold_file

Application.Workbooks.Open (anth_hold_file)
Set anth_temp_file = ActiveWorkbook

''Go back to the main file and update the tab - then copy over to the Dummy file
ext_home_file.Activate
'' Do below 5 times then
....do something to the anth_tab and copy over to dummy file
Sheets(anth_tab.Name).Copy After:=anth_temp_file.Worksheets(anth_temp_file.Worksheets.count)
ext_home_file.Activate
Loop
''After 5 times - Save it and reopen
anth_temp_file.Close SaveChanges:=True
Application.Workbooks.Open (anth_hold_file)
Set anth_temp_file = ActiveWorkbook
ext_home_file.Activate
''Keep looping until it done what it need's to do.
Application.Workbooks.Open (anth_hold_file)
''Remove all formla's PasteSpecial Paste:=xlValues

''Remove range names except for print related
For Each rnge_name_delete In ActiveWorkbook.Names
If Not rnge_name_delete.Name Like "*!Print_Area" And _
Not rnge_name_delete.Name Like "*!Print_Titles" Then
rnge_name_delete.Delete
End If
Next rnge_name_delete

Set anth_temp_file = ActiveWorkbook
For Each hold_sht In ActiveWorkbook.Worksheets
hold_sht.Select False
Next hold_sht
Application.ActiveWorkbook.Windows(1).SelectedSheets.Copy
anth_temp_file.Close SaveChanges:=False
anth_hold_file = anth_hold_file & "." & "*"
Kill anth_hold_file ''(Here's where I get the Permission denied (Error 70)
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
It appears as if anth_hold_file is still open...it must be closed in order to kill it.
 
Upvote 0
It appears as if anth_hold_file is still open...it must be closed in order to kill it.

Thxs for the idea - but anth_hold_file is close via the following stmts
Application.Workbooks.Open (anth_hold_file)
do something.....
Set anth_temp_file = ActiveWorkbook
do something....
anth_temp_file.Close SaveChanges:=False
*****************************************
I also, stepped thru the code to make sure anth_hold_file was not open before the kill statement...

Added - later
Something to think about - it works perfectly in Excel 2003 - why does Excel 2007 have a problem with it ??
 
Last edited:
Upvote 0
Wait a second, DeleteFile() wants a non-wildcarded full File Name. So this would have to change:

anth_hold_file = ActiveWorkbook.Path & "\DELETE_" & Format(Now, "hhmmss")

It needs to be a single, well-defined file. I'm having trouble reading your unformatted code...
 
Upvote 0
my "vba in a nutshell" says kill will allow wildcards, but I've not tested it...

I'm not thrilled by the extra "." here though, what's it for?
Code:
anth_hold_file = anth_hold_file & "." & "*"

Why not?
Code:
anth_hold_file = anth_hold_file & "*"

I can't tell exactly what all your code is doing, but if you are trying to work with multiple files in the folder I'd make sure all files are fully processed and closed, then call a cleanup procedure - best if the cleanup routine is a separate routine.

Other ideas:
Why not just use the same filename for your temp files, and just overwrite each time? Or, create a new directory in code, put the files in it, and delete the directory when finished?
 
Upvote 0
Thxs for the idea - but anth_hold_file is close via the following stmts
Application.Workbooks.Open (anth_hold_file)
do something.....
Set anth_temp_file = ActiveWorkbook
do something....
anth_temp_file.Close SaveChanges:=False

That closes anth_temp_file not anth_hold_file...are they the same. If you are sure anth_hold_file is closed then Im not sure what the issue could be...
 
Upvote 0
NateO:

Thxs for the advice and I will try same

As to the code - It's close to 80-100 lines, I was trying to simpify it to get to the main steps.
Sorry if I didn't provide enough details.

I've chatted w/ you before and your advice\suggestions have proven to be accurate 100% of the time. (this is no suck-up, NateO has always steered me in the right direction)

All I can say is that when I run it in 2003 Excel, it works pefectly, in 2007, it crashes.

I'm lost here...
 
Upvote 0
To all - thxs for all the suggestions - I will try everything

To be frank, I don't expect to resolve the issue. The code worked perfectly in '03, I think there is something in '07 that's different as it handles memory
 
Upvote 0
Or, create a new directory in code, put the files in it, and delete the directory when finished?

Answering my own question, this would require deleting all the files in the folder first...back to square one... :(
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top