Delete a Workbook via code

[Guest]

I'm in need of code to delete a workbook (not the current open workbook).
Thanks!

 

[Crowbar via OfficeKB.com]

Kill "C:\Workbook.xls"

I'm in need of code to delete a workbook (not the current open workbook).
Thanks!

 

[Guest]

This is great. However, is there a way to 'kill' the workbook and it to show
up in the recycle bin as actually being deleted?

Thanks,
Chad

 

[Crowbar via OfficeKB.com]

Thats not quite as simple

But here goes, you need to use windows api

Private Declare Function SHFileOperation Lib "shell32.dll" (ByRef lpFileOp As
SHFILEOPSTRUCT) As Long

Private Const ERROR_SUCCESS = 0&
Private Const FO_COPY = &H2
Private Const FO_DELETE = &H3
Private Const FO_MOVE = &H1
Private Const FO_RENAME = &H4
Private Const FOF_ALLOWUNDO = &H40
Private Const FOF_CONFIRMMOUSE = &H2
Private Const FOF_FILESONLY = &H80
Private Const FOF_MULTIDESTFILES = &H1
Private Const FOF_NOCONFIRMATION = &H10
Private Const FOF_NOCONFIRMMKDIR = &H200
Private Const FOF_RENAMEONCOLLISION = &H8
Private Const FOF_SILENT = &H4
Private Const FOF_SIMPLEPROGRESS = &H100
Private Const FOF_WANTMAPPINGHANDLE = &H20

Private Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Long
hNameMappings As Long
lpszProgressTitle As String ' only used if FOF_SIMPLEPROGRESS
End Type

'Next create a function called Recycle, like so

Public Sub Recycle(ByVal FileName As String)

Dim CFileStruct As SHFILEOPSTRUCT

With CFileStruct
..hwnd = Me.hwnd
..fFlags = FOF_ALLOWUNDO
..pFrom = FileName
..wFunc = FO_DELETE
End With

If SHFileOperation(CFileStruct) <> ERROR_SUCCESS Then
'An error occurred.
End If

End Sub

To test the procedure, create a dummy text file, drop a command button onto a

Visual Basic form, and then attach the following code

Private Sub Command1_Click()
Recycle "c:\test.txt"
End Sub

When you click the button, Windows asks if you want to move the file to the
Recycle Bin.

Regards

Crowbar

 

[Guest]

I did everything you suggested, but I get a compile error that says 'invalid
use of Me keyword.' Is there something in particular I need to do to use
windows api (since you specifically mentioned that)? Any other thoughts?
Thanks!

 

[Doug Glancy]

Chad,

Please be warned that I knew nothing about this API before reading this, and
not that much about API's in general. But if I change one section as
follows, it does work:

With CFileStruct
..hwnd = Application.hwnd
..fFlags = FOF_ALLOWUNDO
..pFrom = FileName
..wFunc = FO_DELETE
End With

Note the dots at the begin of each line between With and End With. For some
reason they don't show in Crowbar's original post of the code, but they do
below in this one, weird. I replaced "Me' with "Application". That
"attaches" the recycle prompt form to Excel, i.e., if you go to another
program and then come back to Excel, the Recycle prompt will still be there.
I think Crowbar used "Me" because he was running this code from a VB
program, not Excel.

One other thing, if you are testing this on an Excel Userform, the click
event would be for "CommandButton1" not "Command1" as it is in VB, which
Crowbar is using.

hth,

Doug

 

[Guest]

This now works perfectly changing the 'me' to 'application.' Thank you both
very much!

-Chad