File delete

[JackDanIce]

Hi,

I have the following code:

Sub DeleteFile ()
Dim TestFile As workbook
Set TestFile = ActiveWorkbook
 
' switch to another defined file
' do some stuff
 
TestFile.Close Save = False
Kill TestFile
 
End Sub

The macro stops at the Kill TestFile line with error message:
"Run-time error '424': Object required"

I can't determine what I'm doing wrong, I thought by setting TestFile as a workbook it is the object?

Thanks,
Jack

 

[VoG]

Try

Sub DeleteFile()
Dim TestFile As Workbook, strFile As String
Set TestFile = ActiveWorkbook
strFile = TestFile.FullName
' switch to another defined file
' do some stuff
 
TestFile.Close Savechanges:=False
Kill strFile
 
End Sub

 

[JackDanIce]

Thanks VoG, that works great

However, I'm already saving the full name of the file into a named range in another workbook. Will the line below work, or can you amend it please so that it does?

Kill Range("NewSummaryFile")

E.g. A1 is renamed as NewSummaryFile and I'm storing the full path name of the file that needs to be deleted in A1

Thanks,
Jack

 

[VoG]

I think that you would need to qualify the workbook and the sheet:

Kill Workbooks("name").Sheets("sheet name").Range("NewSummaryFile")

 

[Aaron.B]

Delete Me

 

[JackDanIce]

Delete me doesn't work

Kill Range("NewSummaryFile") just worked though I am in the activeworkbook where the range is named. I did this yesterday but it didn't work and nothing's changed but oh well it works now!

Thanks,
Jack

 

[Aaron.B]

hahahaha... That was "Delete Me" as in "Please delete this post".

 

[JackDanIce]

lol sorry Aaron - out of interest, how do you delete a post or close it once your query is solved? Always wondered about that..

 

[Aaron.B]

You don't, you acknowledge the problem as solved then leave the thread for other people who have the same problem to find

 

[JackDanIce]

In that case, I believe it's solved! (Well it's currently working anyway...!)
Thanks