Problem deleting file using Excel. 1

[mcelligott]

Here is what I attempting to do, to help you understand my coding:

* I save the file to a new folder using the saveas method (combining three cells to make the name).​

* I verify the file saved to the new folder as expected.​

* I verify the file exists in the old folder as expected (it currently sends a message saying it exists - this is a temporary step).​

* As long as the file exists based on the test in the previous step, I attempt the kill command to delete the file from the old folder.​

but when it actually tries to kill the file, it says "Run-time error 53: File not found".

Sub SaveForOpsMgr()

Dim MyDir As String
Dim MyOldDir As String
Dim MyFile As String
Dim KillFile As String

MyDir = "\\ecd911\911\Operations\DOR - Ops Mgr Review\"
MyOldDir = "\\ecd911\911\Operations\DOR - Supv Review\"
MyFile = Range("B3") & ", " & Range("F3") & " DOR for" & Format(Range("B5"), " mm-dd-yyyy")
KillFile = MyOldDir & MyFile

If IsEmpty(ThisWorkbook.Sheets(1).Range("L57")) Then
    MsgBox "You forgot to sign the DOR."
    ActiveSheet.Range("L57").Select
    GoTo out2
End If

nResult = MsgBox( _
Prompt:="Are you sure ?", _
Buttons:=vbYesNo)
If nResult = vbNo Then
    Exit Sub
Else
    nResult = vbYes
       Application.DisplayAlerts = False
       ActiveWorkbook.SaveAs Filename:=MyDir & MyFile
End If
' Confirm File was saved to the DOR folder
If Dir(MyDir & MyFile) = "" Then
    If Dir(KillFile) = "" Then
        MsgBox KillFile & " exists. Attempting to delete file."
        Kill KillFile
        ActiveWorkbook.Close
    Else
        MsgBox KillFile & " does not exist."
    End If
Else
     MsgBox "DOR did not save!"
End If

out2:
End Sub

I have tried several variations of the same macro code and keep getting the same error. I have also made sure I could manually delete the file from the old folder while the workbook was still open and I was able to do that. I copied the MyFile variable directly from the previous macro that saves it to the old folder initially (so it would be exactly the same). I have also copied and pasted the folder locations directly from windows so there would be no typing errors.

Any help anyone can offer would be greatly appreciated.

 

[combo]

Dir(KillFile) = "" if KillFile does not exist. Does the cell with file name contain proper extension too?

combo

 

[Andrzejek]

I think you have it backwards. Try:

If Dir(KillFile) [red]<>[/red] "" Then
...

Also, when you step thru your code, do you even get to [tt]Kill KillFile[/tt]?

And I am not crazy about your message box. "Are you sure?" Yes/No - Am I sure of what? :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

I think there's a few problems with your code. First of all, the name of the file you will want to delete is the current name of the workbook that is open, not the what you're creating with the MyFile. To get the name, using the code ActiveWorkbook.Name will give you the file name or ActiveWorkbook.FullName will get you the name and directory.

Also, when using the DIR command, you are trying to make certain that
1. The new file exist with a name of MyFile
2. The old file is in the directory of the old file with the filename equal to the file that was originally opened

I would revise the code to the following:

MyOldDir = Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.Name))
KillFile = ActiveWorkbook.Name
.
.
.
If Dir(MyDir & MyFile) = MyFile Then
    If Dir(MyOldDir & KillFile) = KillFile Then

 

[Andrzejek]

I don’t think you can make your file 'commit a suicide', i.e. kill itself.
You will probably get some kind of message: ”Cannot Delete. File is in use.”


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

Andrzejek, you can make it "commit suicide" because you are performing a SAVE AS command and the macro continues on the new file.

 

[Andrzejek]

So it is not a suicide, it is just 'regular' killing the other file

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mintjulep]

There is the name issue that others have identified.

Also, your code may be trying to kill the file faster than excel can clean-up the temp files related to the saveas.

 

[mcelligott]

Thank you everyone for your responses. Zeglar that worked perfectly, you get a star.