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

Kill "C:\Workbook.xls"



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

--
Message posted via http://www.officekb.com

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" wrote:

> Kill "C:\Workbook.xls"
>
>
>
> Chad wrote:
> >I'm in need of code to delete a workbook (not the current open workbook).
> >Thanks!
>
> --
> Message posted via http://www.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

--
Message posted via http://www.officekb.com

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!


"Crowbar via OfficeKB.com" wrote:

> 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
>
> --
> Message posted via http://www.officekb.com
>
>

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

"Chad" <(E-Mail Removed)> wrote in message
news16AC11B-287D-46B9-A6DD-(E-Mail Removed)...
>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!
>
>
> "Crowbar via OfficeKB.com" wrote:
>
>> 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
>>
>> --
>> Message posted via http://www.officekb.com
>>
>>

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

-Chad

"Doug Glancy" wrote:

> 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
>
> "Chad" <(E-Mail Removed)> wrote in message
> news16AC11B-287D-46B9-A6DD-(E-Mail Removed)...
> >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!
> >
> >
> > "Crowbar via OfficeKB.com" wrote:
> >
> >> 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
> >>
> >> --
> >> Message posted via http://www.officekb.com
> >>
> >>
>
>