PDA

View Full Version : If Kill help



Emoncada
01-29-2008, 01:04 PM
I have this Kill vb that I use but would like to know if I can have it in a If Statement.

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

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.


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



How can I do this.

Bob Phillips
01-29-2008, 02:08 PM
Look at the Dir command in help. This will tell you whether a file exists in a directory.

Emoncada
01-29-2008, 03:10 PM
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
01-29-2008, 05:09 PM
If you are moving it, you have to know where it is before the move, so what is the problem?

Emoncada
01-30-2008, 08:15 AM
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
01-30-2008, 10:03 AM
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
01-30-2008, 12:59 PM
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
01-30-2008, 01:00 PM
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
01-30-2008, 01:22 PM
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
01-30-2008, 03:13 PM
How can I do that?

Bob Phillips
01-30-2008, 04:32 PM
Set WB = Workbooks.Open(sFilename)

'do some stuff

sOldName = WB.FullName
WB.SaveAs "somefilename.xls"
Kill sOldName

Emoncada
01-31-2008, 10:40 AM
ok xld so this is a part of the code that saves the spreadsheet to a new location. would this work good like this?

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

Emoncada
01-31-2008, 10:42 AM
If so I will add it also to the rest of the Call Macros from the top of this script.

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

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

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

Bob Phillips
01-31-2008, 10:48 AM
Depends upon what gets loaded into sOldname. It looks about right, but there is no substitute for testing.

Emoncada
01-31-2008, 10:55 AM
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.