Excel macro to kill / delete / self-destruct the same file.

Good afternoon! I have an Excel workbook saved on a network path with a name that appends the current date and time in order to make it unique when created. For example, File 20150417130046.xlsm. As of now, an approver goes into said file and double clicks a cell which automatically appends an approval, saves as a PDF to another drive for processing, and then closes the file. The Excel file is no longer needed after the approval, so it needs to be deleted. We would like to eliminate the risk of manually deleting the wrong file by creating a macro that will automatically delete the Excel file after the PDF is created.

If the file name was static and could be defined, I could run a macro from a separate file (file 2) in order to delete said static file name (file 1). I'm familar with defining names as strings and referencing them later, but then I'm not sure how to take a defined name from the source file (ie the file to be deleted / file 1) and using that name in the macrofile that would perform the termination (file 2).

It is possible to run the kill command, or some kind of self-destruct command, from a macro in the file that I want to delete (ie delete itsself)?
- OR -
Can someone please advise me on how to define the name in file 1 and use that defined name in file 2 in order to delete file 1?

Thanks so much!
LVL 1
Phil_ConsultantAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Try this or add the code to your approval process code.


Sub KillMe()

With ThisWorkbook
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
End With

End Sub

Open in new window

0
 
ThomasMcA2Commented:
As the last step in your macro, append ThisWorkbook.FullName to a "To Be Deleted" text file. Then create a scheduled macro that runs daily (or once an hour), loops through the "To Be Deleted" text file, and deletes the listed files.
0
 
Phil_ConsultantAuthor Commented:
Hello and thank you both for your quick replies!

Martin: I tried your code and it worked, however it then crashed Excel haha... I tried this several times and each time crashed Excel, but the file was deleted.

Thomas: I'm going to try a variation of your suggestion. I'm thinking I'll create that text file, then call out a macro from another file which references the text file to close then delete the original.

I'll revert back later on with an update, but if anyone else has suggestions please let me know! Thanks again.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Martin LissOlder than dirtCommented:
If you're going to use Thomas' method that's fine, but for my own interest could you show me the code that crashed Excel? And BTW what version of Excel are you using?
0
 
Phil_ConsultantAuthor Commented:
I'm using Excel 2010. Ideally I would like to use your solution if Excel wasn't to crash.

'Coding below specifies event to occur when double clicking in a worksheet
'This is achieved by using the setting "Cancel as Boolean"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'DIM = Declaring in memory the labels for variables and then defining the variable type
Dim User_ID As String
Dim Lookup_Range As Range
Dim TeamMember As String
Dim Team As Worksheet
'Defining the sheet to be used for Team information to convert Username to Name
Set Team = ActiveWorkbook.Sheets("Tables")
Set Lookup_Range = Team.Range("A:B")
'Pulling UserID information
User_ID = UCase(Environ("UserName"))
'Converting UserID to Name
TeamMember = Application.WorksheetFunction.VLookup(User_ID, Lookup_Range, 2, False)
'Specifing which specific cell will populate with information when double-clicked (ex. Column 2/Row 20)
If ActiveCell.Column = 3 And ActiveCell.Row = 6 Then
'Specifing information to be included (Name and timestamp) as well as formatting
ActiveCell.Value = TeamMember & " at " & Now()
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
Selection.Locked = True
ActiveCell.Offset(0, 1).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "{File Path Removed} " & Format(Now(), "yyyymmddhhmmss") & ".PDF", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
MsgBox ("The PDF has been saved to the entry folder. It is now safe to close and delete this file.")
End If
'this attempts to delete the file automatically after approval
With ThisWorkbook
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
End With
End Sub
0
 
Martin LissOlder than dirtCommented:
I don't understand why it doesn't work for you since I also use Excel 2010 and I tested it with no problems, but oh well.
0
 
Phil_ConsultantAuthor Commented:
That's odd. I just tried the sub by itsself on a file from my desktop and it worked no trouble at all. I'm going to test different settings such as off the network drive and such and revert back with my findings.
0
 
Martin LissOlder than dirtCommented:
OK thanks. When Excel crashed was there an error message? Also do you know how to use Debug to find out where it is crashing? Here's an article I wrote on debugging but for now if you need help with the debugging, place a breakpoint on the .ChangeFileAccess xlReadOnly line by clicking in the left-hand margin of that line and then press F8 repeatedly to step through the code and see which line causes the failure.
0
 
Phil_ConsultantAuthor Commented:
The .Close False is what crashes my excel so by removing that, the only step I have to take is to close the workbook. I'm playing with ActiveWorkbook.close and other ways to close it at the moment.
0
 
Phil_ConsultantAuthor Commented:
Very weird... If I try any kind of window close / workbook close, etc. then it crashes. If I manually close it (after the kill) then there's no issue at all.
0
 
Phil_ConsultantAuthor Commented:
I also tried introducing a 4 second delay in case the kill was doing something behind the scenes with temporary files/system files but that hasn't helped.

With ThisWorkbook
Application.DisplayAlerts = False
    .ChangeFileAccess xlReadOnly
Application.DisplayAlerts = True
    Kill .FullName
    Application.Wait (Now + TimeValue("00:00:04"))
    '.Close False
    'ActiveWindow.Close
    'ActiveWorkbook.Close
    'Workbooks.Close
End With
End Sub
0
 
Martin LissOlder than dirtCommented:
Try

On Error Resume Next

just before the. close line
0
 
Phil_ConsultantAuthor Commented:
For now I'm confused because the sub by itsself works fine, but appended to my private sub it crashes excel when closing at the end. I'm going to roll with just having it stop the macro without closing Excel, which isn't a big deal at all because the file is already deleted. Maybe I'll revisit this later on. Your macro does accomplish what I needed (other than my system crashing lol!) so thank you very much!
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
 
Phil_ConsultantAuthor Commented:
Interesting thing is if I take the same exact file and create a separate sub, it deletes without crashing. If I put the code within an existing sub, such as the example above, then it crashes. I wonder if it has anything to do with Boolean and the existing private macro. The macro is so that when you double click a cell it embeds user information to that cell. Maybe that private macro needs to finish with an end sub and I can design a button to click for close and delete.
0
 
Martin LissOlder than dirtCommented:
Sounds like a plan.
0
 
Phil_ConsultantAuthor Commented:
One last follow-up before I head to the watering hole:

I modified my existing macro so that after the approval process it creates a button for users to click which activates your delete macro. It works perfectly. My conclusion is that the delete macro needs to be independent of another macro, or at least in this case the macro I pasted above... for whatever reason. I haven't tried a more simple macro, but I'm guessing maybe my private sub or the the contents of the sub caused the crash. Thanks again, your assistance is much appreciated. Hopefully this helps someone else in the future.

Working code, start to finish:

'Coding below specifies event to occur when double clicking in a worksheet
'This is achieved by using the setting "Cancel as Boolean"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'DIM = Declaring in memory the labels for variables and then defining the variable type
Dim User_ID As String
Dim Lookup_Range As Range
Dim TeamMember As String
Dim Team As Worksheet
'Defining the sheet to be used for Team information to convert Username to Name
Set Team = ActiveWorkbook.Sheets("Tables")
Set Lookup_Range = Team.Range("A:B")
'Pulling UserID information
User_ID = UCase(Environ("UserName"))
'Converting UserID to Name
TeamMember = Application.WorksheetFunction.VLookup(User_ID, Lookup_Range, 2, False)
'Specifing which specific cell will populate with information when double-clicked (ex. Column 2/Row 20)
If ActiveCell.Column = 3 And ActiveCell.Row = 6 Then
'Specifing information to be included (Name and timestamp) as well as formatting
ActiveCell.Value = TeamMember & " at " & Now()
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
Selection.Locked = True
ActiveCell.Offset(0, 1).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "{File Path Removed} " & Format(Now(), "yyyymmddhhmmss") & ".PDF", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End If
MsgBox ("The PDF has been saved to the entry folder. Click the button to close and delete this file.")
'inserts a button and setup with delete_me sub
    Range("F45").Select
    ActiveSheet.Buttons.Add(580.5, 421.5, 225.75, 57).Select
    Selection.OnAction = "Sheet1.delete_me"
    Selection.Characters.Text = "Click here to close and delete the file."
    With Selection.Characters(Start:=1, Length:=40).Font
        .Name = "Calibri"
        .FontStyle = "Regular"
        .Size = 11
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("F48").Select
End Sub


Sub delete_me()
'deletes the file
With ThisWorkbook
Application.DisplayAlerts = False
    .ChangeFileAccess xlReadOnly
Application.DisplayAlerts = True
    Kill .FullName
   Application.Wait (Now + TimeValue("00:00:03"))
    .Close False
End With
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.