Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
I'm in need of code to delete a workbook (not the current open workbook).
Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete a Workbook via code
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete comments in the workbook except few cells: CODE given | Excel Programming | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) | |||
Automatically Delete WorkBook 2 modules by using Workbook 1 module | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming | |||
VBA code to delete VBA code in another Workbook | Excel Programming |