How to delete my excel file in a network

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hello,

I want to create a macro that would delete or remove my
backup excel file in a network after I exit from excel
worksheet. How could I do that? I would appreciate your
help.

Thanks
Eric
 
If you want to delete to the recycle bin you could use the following code,
using the Windows API.
It is an adaptation of code I found on the VB site of Randy Birch. As others
may find this code useful
I thought it might be worth posting. What is nice about this code is that it
can show the flying files and
also that it will give the proper Windows error messages for example if a
file can't be deleted because it
is in use.
I am not sure if there is a simpler way to delete to the recycle bin.


Option Explicit

Public 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

Public Const FOF_SILENT As Long = &H4
Public Const FOF_RENAMEONCOLLISION As Long = &H8
Public Const FOF_NOCONFIRMATION As Long = &H10
Public Const FOF_SIMPLEPROGRESS As Long = &H100
Public Const FOF_ALLOWUNDO As Long = &H40

Public Declare Function SHFileOperation _
Lib "shell32" _
Alias "SHFileOperationA" _
(lpFileOp As SHFILEOPSTRUCT) As Long

Function FileActions(sSource As String, _
sDestination As String, _
iMoveCopyDeleteRename As Integer, _
bSilent As Boolean, _
bNoFilenames As Boolean, _
bNoConfirmDialog As Boolean, _
bRenameIfExists As Boolean, _
bAllowUndo) As Long

'adapted code from Randy Birch at:
'http://vbnet.mvps.org/index.html?code/shell/shdirectorycopy.htm
'----------------------------------------------------------------
'sSource is the source folder as a string
'sDestination is the destination folder as a string

'iMoveCopyDeleteRename has these option:
'1 for moving
'2 for copying
'3 for deleting
'4 for renaming

'bSilent: if FALSE the file progress will show
'bNoFilenames: if TRUE no file names will show in the progress
'bNoConfirmDialog: if TRUE there will be no warning when
'overwriting files
'bRenameIfExists: if TRUE there will be renaming if there were
'going to be files overwritten
'bAllowUndo: this applies when deleting files(3) is choosen
'if TRUE the files will go to the Recycle bin

'will return 0 if successful
'----------------------------------------------------------------

Dim FOF_FLAGS As Long
Dim SHFileOp As SHFILEOPSTRUCT

'terminate the folder string with a pair of nulls
sSource = sSource & Chr$(0) & Chr$(0)

'determine the user's options selected
FOF_FLAGS = BuildBrowseFlags(bSilent, _
bNoFilenames, _
bNoConfirmDialog, _
bRenameIfExists, _
bAllowUndo)

'set up the options
With SHFileOp
.wFunc = iMoveCopyDeleteRename
.pFrom = sSource
.pTo = sDestination
.fFlags = FOF_FLAGS
End With

'and perform the chosen copy or move operation
FileActions = SHFileOperation(SHFileOp)

End Function


Function BuildBrowseFlags(bSilent, _
bNoFilenames, _
bNoConfirmDialog, _
bRenameIfExists, _
bAllowUndo) As Long

Dim flag As Long

'these can be multiple
If bSilent = True Then
flag = flag Or FOF_SILENT
End If

If bNoFilenames = True Then
flag = flag Or FOF_SIMPLEPROGRESS
End If

If bNoConfirmDialog = True Then
flag = flag Or FOF_NOCONFIRMATION
End If

If bRenameIfExists = True Then
flag = flag Or FOF_RENAMEONCOLLISION
End If

If bAllowUndo = True Then
flag = flag Or FOF_ALLOWUNDO
End If

BuildBrowseFlags = flag

End Function


Sub DeleteToRecycleBin()

Dim lRetVal As Long

'I don't think it matters here what the second argument is
'------------------------------------------------------------------
lRetVal = FileActions("C:\TestFile", _
"C:", _
3, _
False, _
False, _
True, _
False, _
True)

End Sub



RBS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top