Kill a Workbook

[Maninder Karir]

Hi,

Im having trouble deleting a workbook using VBA. What i want is to open a workbook called "xxxxx waiting", make some modification and then save it as readonly but as a "xxxxx complete". I then want to delete the old workvook "xxxxx waiting"

I though i had it cracked but it doesnt seem to work. Ive put the code below if someone could help.

Thanks in advance

Private Sub Save_Click()

ReqNum = Worksheets("che micals").Range( "D2")

With ActiveWorkbook
.SaveAs Filename:="Z:\R eq\" & ReqNum & "Complete", _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="", _
ReadOnlyRecomme nded:=True, CreateBackup:=F alse

End With

Kill "Z:\Req\" & ReqNum & "Waiting"

End Sub
Manny

[SammyB]

I don't think that you can kill yourself. That would be dangerous. :D You'll need another program to open the workbook, save as, close, and kill.

[Killer42]

Can you be a bit more specific than "doesn't work"? What does happen?

[fplesco]

Hi,

Im having trouble deleting a workbook using VBA. What i want is to open a workbook called "xxxxx waiting", make some modification and then save it as readonly but as a "xxxxx complete". I then want to delete the old workvook "xxxxx waiting"

I though i had it cracked but it doesnt seem to work. Ive put the code below if someone could help.

Thanks in advance

Private Sub Save_Click()

ReqNum = Worksheets("che micals").Range( "D2")

With ActiveWorkbook
.SaveAs Filename:="Z:\R eq\" & ReqNum & "Complete", _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="", _
ReadOnlyRecomme nded:=True, CreateBackup:=F alse

End With

Kill "Z:\Req\" & ReqNum & "Waiting"

End Sub
Manny

Hi there -

You might miss the extension of the file

Try this:

Kill "Z:\Req\" & ReqNum & "Waiting.xls"

Have a nice day!

[fplesco]

Hi,

Im having trouble deleting a workbook using VBA. What i want is to open a workbook called "xxxxx waiting", make some modification and then save it as readonly but as a "xxxxx complete". I then want to delete the old workvook "xxxxx waiting"

I though i had it cracked but it doesnt seem to work. Ive put the code below if someone could help.

Thanks in advance

Private Sub Save_Click()

ReqNum = Worksheets("che micals").Range( "D2")

With ActiveWorkbook
.SaveAs Filename:="Z:\R eq\" & ReqNum & "Complete", _
FileFormat:=xlN ormal, Password:="", WriteResPasswor d:="", _
ReadOnlyRecomme nded:=True, CreateBackup:=F alse

End With

Kill "Z:\Req\" & ReqNum & "Waiting"

End Sub
Manny

Hi there -

You might miss the extension of the file and the space between the ReqNum and Waiting:

Try this:

Kill "Z:\Req\" & ReqNum & " Waiting.xls"   ' put a single SPACE before Waiting

Have a nice day!

[Maninder Karir]

Hi there -

You might miss the extension of the file

Try this:

Kill "Z:\Req\" & ReqNum & "Waiting.xls"

Have a nice day!

Thanks worked a treat!!!!

[Killer42]

... You might miss the extension of the file and the space between the ReqNum and Waiting ...

Thanks again, fplesco.

I'm curious though - are you sure about the space? I've always used Format$() function when printing or concatenating numbers, specifically to prevent the insertion of the extra space. Can never remember though, whether it's before or after the number. (I guess I should go and check.)

[Killer42]

... I've always used Format$() function when printing or concatenating numbers, specifically to prevent the insertion of the extra space. Can never remember though, whether it's before or after the number. (I guess I should go and check.)

Well, I just set up this elaborate testing scenario...

? 5;"h";6;"f";7

which produced the output " 5 h 6 f 7 and confirmed that printing, at least, leaves a space before the number.

But of course (and I'm sure you know where this is heading) a quick test with this code...[CODE=vb]Dim A As String
A = 5 & "h" & 6 & "f" & 7
Debug.Print A[/CODE]...produced "5h6f7", thus refuting my "extra space" theory. Obviously I've been using Format unnecessarily. Slightly annoying, but I guess it's good that I know better now.