kill command and excel

[charles]

I would like to use the Kill command to delete a file whose name and location
have been selected in a drop down box.

ie the drop down box named "filetodestroy" has the following selected
Crograms/mycrap/test.xls

what syntax do u use to make the kill command destroy the file selected?

 

[Hank Scorpio]

I would like to use the Kill command to delete a file whose name and location
have been selected in a drop down box.

ie the drop down box named "filetodestroy" has the following selected
Crograms/mycrap/test.xls

what syntax do u use to make the kill command destroy the file selected?

It's just
Kill pathname

but at a guess your problem is that it should be
C:\programs\mycrap\test.xls

(That is, you're missing the first slash after the drive. Generally
the path will use backslashes rather than forward slashes as well,
though either will work with Kill.)

You may also want to use
On Error Resume Next
before the command and
On Error GoTo 0
after it to temporarily turn off error handling. That way if the file
isn't there, you won't be bugged with an error message.

 

[charles]

thanks hank.
but that is not the information that i need.

I want to be able to select the file that i want destroyed from a list of
files in a drop down box. And have the kill command destroy the file that is
selected in the drop down box. The drop down box name is "filetodestroy"

 

[Hank Scorpio]

thanks hank.
but that is not the information that i need.

I think you need to clarify exactly what information it is that you DO
need; your actual question was "what syntax do u use to make the kill
command destroy the file selected?", and that would indeed be

Kill "C:\programs\mycrap\test.xls"

Taking another guess at what you need, and assuming that the list is a
Control Toolbox combo box, you could use the combo box's Change event
to drive the deletion based on the user's selection. Like so:

Private Sub filetodestroy_Change()
Dim s_FileDir As String
Dim l_mbresp As Long

'Check that there's a selection.
If filetodestroy.ListIndex <> -1 Then

'Confirm that the file still exists.
s_FileDir = Dir(filetodestroy.Text)

If s_FileDir = "" Then
MsgBox "The file " & filetodestroy.Text _
& " no longer exists.", vbInformation

'Don't want this triggering another change event.
Application.EnableEvents = False

'Remove the non-existent file from the list and
'clear the entry.
With filetodestroy
.RemoveItem filetodestroy.ListIndex
.Text = ""
End With
Application.EnableEvents = True
Else
'If the file DOES exist, confirm that we want
'to delete it.

l_mbresp = MsgBox("Are you sure that you want to delete file "
_
& filetodestroy.Text & "?", vbYesNo + vbDefaultButton2)

If l_mbresp = vbYes Then
Kill filetodestroy.Text
Application.EnableEvents = False
With filetodestroy
.RemoveItem filetodestroy.ListIndex
.Text = ""
End With
Application.EnableEvents = True
End If

End If

End If

End Sub

On the other hand if you don't want to drive the deletion straight off
the selection and want to just read the file name from the list box
(so that some other code can delete it), then filetodestroy.Text would
get you the name to feed to the Kill statement.

If that's not it, please clarify.

 

[JLatham]

Well, I suppose you could use something like
Kill filetodestroy.Text

Or you might be able to make use of this code which allows you to select a
file using the file picker dialog, and in this case, Kill the file selected:

Sub KillAFile()
Dim Response As String
Response = SelectFileForUse()
If Response <> "" then
Kill Response
End If
End Sub

Function SelectFileForUse() As String
'either return a full well-formed path & filename
'to file selected, or "" if user cancels.
'
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
If .Show = -1 Then
'user chose a file
'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected
item.
'You can use any file I/O functions that you want to work with this
path.
SelectFileForUse = vrtSelectedItem
Next vrtSelectedItem
'The user pressed Cancel.
Else
SelectFileForUse = ""
End If
End With

Set fd = Nothing ' good housekeeping
End Function

 

[charles]

I think you need to clarify exactly what information it is that you DO
need; your actual question was "what syntax do u use to make the kill
command destroy the file selected?", and that would indeed be

Kill "C:\programs\mycrap\test.xls"

Taking another guess at what you need, and assuming that the list is a
Control Toolbox combo box, you could use the combo box's Change event
to drive the deletion based on the user's selection. Like so:

Private Sub filetodestroy_Change()
Dim s_FileDir As String
Dim l_mbresp As Long

'Check that there's a selection.
If filetodestroy.ListIndex <> -1 Then

'Confirm that the file still exists.
s_FileDir = Dir(filetodestroy.Text)

If s_FileDir = "" Then
MsgBox "The file " & filetodestroy.Text _
& " no longer exists.", vbInformation

'Don't want this triggering another change event.
Application.EnableEvents = False

'Remove the non-existent file from the list and
'clear the entry.
With filetodestroy
.RemoveItem filetodestroy.ListIndex
.Text = ""
End With
Application.EnableEvents = True
Else
'If the file DOES exist, confirm that we want
'to delete it.

l_mbresp = MsgBox("Are you sure that you want to delete file "
_
& filetodestroy.Text & "?", vbYesNo + vbDefaultButton2)

If l_mbresp = vbYes Then
Kill filetodestroy.Text
Application.EnableEvents = False
With filetodestroy
.RemoveItem filetodestroy.ListIndex
.Text = ""
End With
Application.EnableEvents = True
End If

End If

End If

End Sub

On the other hand if you don't want to drive the deletion straight off
the selection and want to just read the file name from the list box
(so that some other code can delete it), then filetodestroy.Text would
get you the name to feed to the Kill statement.

If that's not it, please clarify.

 

[charles]

Thanks Hank.
I believe the information that you provided in reguards to using a change
event for my combo box will work.
chuck

 

[charles]

thank you for taking the time to respond.

Well, I suppose you could use something like
Kill filetodestroy.Text

Or you might be able to make use of this code which allows you to select a
file using the file picker dialog, and in this case, Kill the file selected:

Sub KillAFile()
Dim Response As String
Response = SelectFileForUse()
If Response <> "" then
Kill Response
End If
End Sub

Function SelectFileForUse() As String
'either return a full well-formed path & filename
'to file selected, or "" if user cancels.
'
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
If .Show = -1 Then
'user chose a file
'Step through each string in the FileDialogSelectedItems collection.
For Each vrtSelectedItem In .SelectedItems
'vrtSelectedItem is a String that contains the path of each selected
item.
'You can use any file I/O functions that you want to work with this
path.
SelectFileForUse = vrtSelectedItem
Next vrtSelectedItem
'The user pressed Cancel.
Else
SelectFileForUse = ""
End If
End With

Set fd = Nothing ' good housekeeping
End Function