Get a macro to delete it's file when closed

[Saa62]

I know this may sound strange, but I want to be able to delete the file after the macro has been run. Part of the macro makes a copy of the file into another folder. The original file needs to be deleted after the file has been copied. Is this possible?

Thanks

Scott

 

[Legacy 98055]

Hi
I'm not sure how you would do that...
You cannot delete an open file.
Therefor, a file cannot delete it's self.
You might want to activate the copy, and then run a macro which would close the original workbook and delete it...
Of course these instructions would need to be placed in a contitional If...Then statement...
Am curious about this one...
Will try and see if it can be done with what I know???
Tom

 

[Legacy 98055]

Hi
I figured out a way to do it...
But I am going to need to post a topic as well..
The macro which saves the copy of the activeworkbook to another folder can create a text file which can be imported via the open statement of the copy it's self.
I'm pretty sure you can import text into a VBA project via code. Will find out.
Tom

 

[Jay Petrulis]

Hi,

It is possible.

Check the following thread:

http://groups.google.com/groups?hl=en&selm=8tvih2$7r1$1@nnrp1.deja.com

In a later post, Chip Pearson posted the routine to do it (original credit to Jim Rech).

'----------------
Sub Suicide()
Dim FName As String
Dim Ndx As Integer
With ThisWorkbook
.Save
For Ndx = 1 To Application.RecentFiles.Count
If Application.RecentFiles(Ndx).Path = .FullName Then
Application.RecentFiles(Ndx).Delete
Exit For
End If
Next Ndx
.ChangeFileAccess Mode:=xlReadOnly
Kill .FullName
.Close SaveChanges:=False
End With
End Sub
'------------------

Bye,
Jay

 

[Mark O'Brien]

Nice one, Jay. I was setting up to have a crack at this one, then read TsTom's reply to his own post. (related to this post)

EDIT:: I also read the whole thread. Nice to see MS Excel MVP's getting slapping each other around.
_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-22 18:51

 

[Legacy 98055]

Thanks for the reply Jay...
Will put that block in Tom's "neat stuff" workbook.
Thanks,
Tom

 

[Colo]

Hi all. I also thought by another method.

Sub Suicide2()
Dim NB As Object
Set NB = Workbooks.Add
With Application
.DisplayAlerts = False
With ThisWorkbook
Open .Path & "xx.bas" For Output As #1
Print #1, "Sub Temp"
Print #1, "Workbooks(" & """" & .Name & """" & ").Close False"
Print #1, "Kill " & """" & .Path & "" & .Name & """"
Print #1, "Kill " & """" & .Path & "xx.bas" & """"
Print #1, "ThisWorkbook.Close False"
Print #1, "End Sub"
Close #1
NB.VBProject.VBComponents.Import FileName:=.Path & "xx.bas"
End With
.OnTime Now(), NB.Name & "!Temp"
.DisplayAlerts = True
End With
End Sub

Regards,
Colo

 

[Saa62]

This site is amazing. It has saved me days worth of work. Many thanks to all who have helped. Everyone I work with thinks I am a genious to get excel to do all these neat tricks. I do give credit to all of you that helped. Due to newly received prioities for me, it may be awhile before I will be able to get this information implemented, but I am sure it will work, everthing else that I have gotten from this site has worked great.

Thanks again

Scott

 

[Jay Petrulis]

On 2002-04-23 02:55, Saa62 wrote:
This site is amazing. It has saved me days worth of work. Many thanks to all who have helped. Everyone I work with thinks I am a genious to get excel to do all these neat tricks. I do give credit to all of you that helped. Due to newly received prioities for me, it may be awhile before I will be able to get this information implemented, but I am sure it will work, everthing else that I have gotten from this site has worked great.

Thanks again

Scott

Sorry to bring this up to the top of the board (I was searching for this routine and saw the follow up posts)

Scott has broken a cardinal rule with his colleagues at work...

Never, never, never give the credit to anyone else, that is what blame is for!!! Take *ALL* the glory yourself.

Of course, recommend the site later, but you are a genius at work because of *your* efforts. Deny all when you are asked if you received any help.



Bye,
Jay
This message was edited by Jay Petrulis on 2002-04-29 18:39

 

[Anne Troy]

I agree with Jay.

The thing is...

It's soooo unbelievable how many people DON'T KNOW ABOUT ALL THE FREE TECH SUPPORT SITES!

Or it's against their religion or something to use them. (Actually, I think it's their egos getting in the way---asking for help is very hard for some people to do.)