Deleteing a file in VBA

[AJSatori]

I am tring to delete a file in one folder after I have saved it into another. Any suggestions?

 

[TommyGun]

Here ya go...

Kill "C:\YourFile.xls"

 

[AJSatori]

Ok, so how would I "kill" the workbook that I am working on?

 

[Joe4]

What do you mean? I don't think you can delete the current file while you are working on it.

Judging from your previous post, if you mean delete it form the previous directory after you saved it to a new one, then use can use:

Kill "C:\" & ThisWorkbook.Name

(this example assumes C:\ is the old directory, changes yours to fit_

 

[TommyGun]

You cannot delete a file that is open. Here's a some examples of how to "move" a file into a new directory.

Sub OpenandRename()

Workbooks.Open "C:\Test\OldFile.xls"

Activeworkbook.SaveAs FileName:="C:\New\NewFile.xls"
Activeworkbook.Close False

Kill "C:\Test\OldFile.xls"

End Sub

Another way using the name statement.

Sub MoveFile()

Name "C:\Test\OldFile.xls" as "C:\New\NewFile.xls"

End Sub

NOTE: Haven't tested, but both should work.

 

[Tom Urtis]

Yes you can delete an open file or ThisWorkbook active file, and here is how.

One friendly piece of advice, make **** sure you save a copy of your victim workbook beforehand, in case you change your mind after the suicide code is executed.

'''''''''''''''''''''''''''''''''''''''''''''

Sub Suicide()
Dim KillShot As Integer
KillShot = MsgBox("Wanna kill this file?" & vbCrLf & "Be sure to make a backup if you change your mind later.", 36, "Feeling lucky?")
Select Case KillShot
Case 7
MsgBox "You live another day.", 64, "Spared the gallows, kill cancelled."
Exit Sub
Case 6
MsgBox "Click OK to flip the kill switch.", 48, "I hope you know what you're doing."
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End Select
End Sub

 

[AJSatori]

thanks I really appriciate it

 

[Joe4]

Tom,

I didn't know that. Interesting, but SCARY...

 

[TommyGun]

I stand corrected.

 

[Tom Urtis]

As I PM'd to TG, a little knowledge can be a dangerous thing. However, it's a good practice to responsibly to learn the boundaries of Excel's limitations, not just what you read in books. If nothing else, you'll be more aware of what the bad guys can do, to at least stay on your guard for yourself and your clients.