gemnnsw, here is one way, put in your personal workbook code
Option Explicit
'delete open file in Excel, run DeleteThisFile
'By: Jonathan West
'This code is based on Randy Birch's article at
'
http://www.mvps.org/vbnet/index.html?code/shell/recyclebin.htm
Private Type SHFILEOPSTRUCT
hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAborted As Boolean
hNameMaps As Long
sProgress As String
End Type
Private Const FO_DELETE As Long = &H3
Private Const FOF_ALLOWUNDO As Long = &H40
Private Const FOF_NOCONFIRMATION = &H10 ' Don't prompt the user
Private Declare Function SHFileOperation Lib "shell32" _
Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As Long
Public Sub SendToRecycle(sFile As String, Optional bConfirm As Boolean =
False)
'set some working variables
Dim shf As SHFILEOPSTRUCT
'terminate the file string with
'a pair of null chars
sFile = sFile & vbNullChar & vbNullChar
'set up the SHFile options
With shf
.wFunc = FO_DELETE 'action to perform
.pFrom = sFile 'the file to act on
If bConfirm Then
.fFlags = FOF_ALLOWUNDO 'special flags
Else
.fFlags = FOF_ALLOWUNDO Or FOF_NOCONFIRMATION 'special flags
End If
End With
'perform the delete
Call SHFileOperation(shf)
End Sub
Sub DeleteThisFile()
Dim MyFile As String
MyFile = ActiveWorkbook.Path + "\" + ActiveWorkbook.Name
If MsgBox(MyFile + " will be deleted !" & vbLf & vbLf & _
" Do you want to delete this file?", _
vbYesNo, "Delete this File?") = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.Close
SendToRecycle MyFile
'***comment above and uncomment below to bypass the recycling
bin
'Kill MyFile
End If
Application.DisplayAlerts = True
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
