Can anyone please advise how to delete excel spreadsheets within excel,
rather than doing this through windows explorer. This would be
particularly useful after viewing a spreadsheet and then deciding it
was no longer needed and could be deleted on the spot.


--
gemnnsw
------------------------------------------------------------------------
gemnnsw's Profile: http://www.excelforum.com/member.php...o&userid=30456
View this thread: http://www.excelforum.com/showthread...hreadid=501225

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

"gemnnsw" wrote in
message ...

Can anyone please advise how to delete excel spreadsheets within excel,
rather than doing this through windows explorer. This would be
particularly useful after viewing a spreadsheet and then deciding it
was no longer needed and could be deleted on the spot.


--
gemnnsw
------------------------------------------------------------------------
gemnnsw's Profile:
http://www.excelforum.com/member.php...o&userid=30456
View this thread: http://www.excelforum.com/showthread...hreadid=501225