If Kill help

[Emoncada]

I have this Kill vb that I use but would like to know if I can have it in a If Statement.

[VBA]Kill ("S:\Equipment Tracking\WIP\" & Range("K3").Value & "\" & Range("K5").Value & "\" & Range("B6").Value & ".xls")[/VBA]

So I would like for it to locate the file first If it's not in that "WIP" Folder To look for it In the ("S:\Equipment Tracking\Available") Folder.

Or have it test a range of cells to know in which folder it's in.

[vba]
If
Test Range ("J27") If = "Available" then Look in Available Folder.
ElseIF
Test Range ("J27") If = "Retire" then Look in Retire Folder.
ElseIF
Test Range ("J27") If = Anything other than "Retire","Available", "" then Look in WIP Folder.
ElseIF
Test Range ("J27") If = "" then Go To Next Range

ElseIF
Test Range ("J23") If = "Available" then Look in Available Folder.
ElseIF
Test Range ("J23") If = "Retire" then Look in Available Folder.
ElseIF
Test Range ("J23") If = Anything other than "Retire","Available", "" then Look in WIP Folder.
ElseIF
Test Range ("J23") If = "" then Go To Next Range.

ElseIF
Test Range ("J19") If = "Available" then Look in Available Folder.
ElseIF
Test Range ("J19") If = "Retire" then Look in Available Folder.
ElseIF
Test Range ("J19") If = Anything other than "Retire","Available", "" then Look in WIP Folder.
ElseIF
Test Range ("J19") If = "" then Go To Next Range.

ElseIF
Test Range ("J15") If = "Available" then Look in Available Folder.
ElseIF
Test Range ("J15") If = "Retire" then Look in Available Folder.
ElseIF
Test Range ("J15") If = Anything other than "Retire","Available", "" then Look in WIP Folder.
ElseIF
Test Range ("J15") If = "" then Go To Next Range.

ElseIF
Test Range ("J11") If = "Available" then Look in Available Folder.
ElseIF
Test Range ("J11") If = "Retire" then Look in Available Folder.
ElseIF
Test Range ("J11") If = Anything other than "Retire","Available", "" then Look in WIP Folder.
ElseIF
Test Range ("J11") If = "" then

End Sub

[/vba]

How can I do this.

[Bob Phillips]

Look at the Dir command in help. This will tell you whether a file exists in a directory.

[Emoncada]

Well let me explain what this spreadsheet does. Data is input into this spreadsheet and saved then it's moved to a folder depending on what a value in a cell is. I need it to know where it was before so it can kill it how can i do that?

[Bob Phillips]

If you are moving it, you have to know where it is before the move, so what is the problem?

[Emoncada]

no I wasn't able to use the move function what it does is saves it in a new location based on the input. Then I need for the spreadsheet to know where it originally was so it can kill that file.

[Bob Phillips]

So you must have opened it which means that you knew where it was. You can't manage a file without at some stage knowing where it is.

[Emoncada]

I know what you mean xld but I don't want the user to have to go back into where ever the file was and delete it. I would like for it to auto delete after file was saved in the new location.

[Emoncada]

Is it possible for when application is opened to paste the location where it's located then when saved have it look at that location for it and kill it?

[Bob Phillips]

No I am saying that in the code at sme point you must have a handle to the original workbook. Save that handle and use it later when you need to delete it.

Or is there some other process that I am missing?

[Emoncada]

How can I do that?

[Bob Phillips]

[vba]

Set WB = Workbooks.Open(sFilename)

'do some stuff

sOldName = WB.FullName
WB.SaveAs "somefilename.xls"
Kill sOldName
[/vba]

[Emoncada]

ok xld so this is a part of the code that saves the spreadsheet to a new location. would this work good like this?

[vba]If Range("AA1").Value = "Available" Then
Call SaveAvailable

ElseIf Range("AA1").Value = "Retire" Then
Call SaveRetire

ElseIf Range("AA1").Value = "Shipping" Then
Call SaveShipped

'-------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------

Else
Dim fp As String, strSaveFile As String


Set WB = Workbooks.Open(sFilename)


sOldName = WB.FullName



FilePath = ""
'fp = "C:\Equipment Tracking\WIP\"
fp = "S:\Equipment Tracking\"
Call MakeFolders(fp)
Call MakeFolders(Range("AA1") & "\")
Call MakeFolders(Range("K3") & "\")
Call MakeFolders(Range("K5") & "\")


Application.DisplayAlerts = False
strSaveFile = Range("B6").Value & ".xls"
Application.DisplayAlerts = True

ActiveWorkbook.SaveAs FilePath & strSaveFile, xlWorkbookNormal
FilePath = ""

'Kill ("C:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")
'Kill ("S:\Equipment Tracking\Needs Approval\" & Range("V1").Value & "\" & Range("B6").Value & ".xls")

Kill sOldName

End If

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True

ActiveWorkbook.Save
ActiveWorkbook.Close



Case vbCancel

Exit Sub

End Select

End Sub[/vba]

[Emoncada]

If so I will add it also to the rest of the Call Macros from the top of this script.
[vba]
If Range("AA1").Value = "Available" Then
Call SaveAvailable

ElseIf Range("AA1").Value = "Retire" Then
Call SaveRetire

ElseIf Range("AA1").Value = "Shipping" Then
Call SaveShipped
[/vba]

[Bob Phillips]

Depends upon what gets loaded into sOldname. It looks about right, but there is no substitute for testing.

[Emoncada]

It's giving me a runtime error " could not be found. Check the spelling of the file name , and verify that the file location is correct.