Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Get a macro to delete it's file when closed

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    It is possible.

    Check the following thread:

    http://groups.google.com/groups?hl=e...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

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-22 18:51 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.)
    ~Anne Troy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •