File delete

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
Hi,

I have the following code:

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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

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

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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?

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

Legend
Joined
Jun 19, 2002
Messages
63,651
I think that you would need to qualify the workbook and the sheet:

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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

Board Regular
Joined
Jan 13, 2010
Messages
60
hahahaha... That was "Delete Me" as in "Please delete this post".
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
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

Board Regular
Joined
Jan 13, 2010
Messages
60
You don't, you acknowledge the problem as solved then leave the thread for other people who have the same problem to find:)
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,334
In that case, I believe it's solved! (Well it's currently working anyway...!)
Thanks
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top