Macro To Make a file delete itself

[davisto01]

I have an excel sheet that is being generated daily by MS Access. I have searched about a dozen forums and no one seems to know how to bypass the 'overwrite' prompts access gives you.

Since the worst case scenario has reered its ugly head, I have decided that it might be effective to simply have the file delete itself after it opens (opens on a timer and sends a copy of itself by email)

Does anyone have a solution to either issue? (Especially the first one).

 

[Bowers74]

I'm assuming that Access VBA doesn't have an

Application.DisplayAlerts = False

function like Excel does.

If not, then I'm afraid I can't help you on the first problem.

The second problem is impossible from within the Excel File itself, but you can delete the file from the Access VBA:

add

Kill "C:\ExcelFile.xls"

after your code that sends the email.


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAjedi]

Well, if you're just creating a new Excel file from Access every day and saving it as the same filename (overwriting yesterdays copy): delete yesterday's copy FIRST (using Mike's code), then create today's copy.

Kaboom - No annoying "Overwrite?" popup!

Let me know if this works for you.



VBAjedi

 

[TonyJollans]

Hi davisto01,

If it helps, the Access equivalent of the Excel Application.DisplayAlerts is DoCmd.SetWarnings = False

Enjoy,
Tony

 

[Chattin]

I've had this xl macro in my personal.xls for a while but never tested it so have a (careful) play

Sub Suicide()
'Workbook deletes itself
Dim KillShot As Integer
KillShot = MsgBox("Wanna kill this file?" & vbCrLf & "Be sure to make a backup if you change your mind later.", 36, "Feeling lucky?&quot
Select Case KillShot
Case 7
MsgBox "You live another day.", 64, "Spared the gallows, kill cancelled."
Exit Sub
Case 6
MsgBox "Click OK to flip the kill switch.", 48, "I hope you know what you're doing."
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Select
End Sub

HTH