Delete file, based on cell name

[tm0206]

Hi,
I am looking for a VBA to delete a files, with the name of the file coming from a cell value.

Cell A1 = names
I would like to delete the file "names" from c:\inventory\data

I am think of using, but not sure how to include the cell into the statement?
kill c:\inventory\data\"A1".xls

 

[pedie]

kill "c:\inventory\data\"& range("A1").value & ".xls"

 

[tm0206]

Worked great, thanks for the quick response.

 

[scubadunc]

kill "c:\inventory\data"& range("A1").value & ".xls"

This works great for a single cell, but I thought I may be able to use the following for a range of cells:

[FONT=&quot]kill "c:\inventory\data"& [/FONT]Range("A2:A271").Value

But I keep getting Type Mismatch errors

Any idea if this is possible?

 

[Rick Rothstein]

This works great for a single cell, but I thought I may be able to use the following for a range of cells:

[FONT="]kill "c:\inventory\data"& [/FONT]Range("A2:A271").Value

But I keep getting Type Mismatch errors

Any idea if this is possible?[/QUOTE]
Kill only handles one file at a time. You could modify your code to loop through all of the cells, but here is a completely different method that should work. Note that Kill and the following method both permanently deletes files... they will be unrecoverable once deleted using either method so I would suggest you set up a test directory and copy some files into it and they use the following code to delete them. Once you are convinced the code works correctly, then you can turn it loose on your actual data.
[CODE][table="width: 500"]
[tr]
[td]Sub DeleteMultipleFiles()
Shell Environ("comspec") & " /c DEL /Q " & """" & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), """ """) & """", vbHide
End Sub[/td]
[/tr]
[/table]
[/CODE]

 

[scubadunc]

Kill only handles one file at a time. You could modify your code to loop through all of the cells, but here is a completely different method that should work. Note that Kill and the following method both permanently deletes files... they will be unrecoverable once deleted using either method so I would suggest you set up a test directory and copy some files into it and they use the following code to delete them. Once you are convinced the code works correctly, then you can turn it loose on your actual data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteMultipleFiles()
  Shell Environ("comspec") & " /c DEL /Q " & """" & Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), """ """) & """", vbHide
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the update, I'm a bit of a newbie...Where would I put the file location info in your string?

 

[Rick Rothstein]

Thanks for the update, I'm a bit of a newbie...Where would I put the file location info in your string?

The file list is assumed to be one file path/name per cell starting at cell A2 and listed downward. This is the same range you indicated in Message #4 although you gave a fixed range of A2:A271 whereas the code I posted figures out the last cell with data in Column A by itself. So, all you have to do is list the files to be deleted and run the macro.

 

[scubadunc]

I'm referring to the file location information. In the original code, we state the actual folder "c:\inventory\data". Where do I put the folder name in your new code?

 

[Rick Rothstein]

I'm referring to the file location information. In the original code, we state the actual folder "c:\inventory\data". Where do I put the folder name in your new code?

Ah, I see... I thought the folder name was in the cell. Okay, in order to be able to modify my code for you, I need you to clarify something for me. In Message #4 , you showed this line of code...

kill "c:\inventory\data"& Range("A2:A271").Value

I note that there is no backslash after the word "data"... does that mean the word "data" is part of the file name that gets created by concatenating the word "data" onto whatever is in each cell in Column A? In other words, if the contained this...

SomeText.txt

would the filename to be deleted be this...

dataSomeText.txt

or did you forget to put a backslash after the word "data"?

 

[scubadunc]

Sorry, I forgot to put a backslash after the word data