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)
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:
