delete workbook from one location and save workbook to new locatio

[Guest]

I need to be able to save a workbook to a new location and delete the same
workbook from the old location, if not delete then move the file to another
folder.
I am able to save a file using a named range but with my code I can't seem
to be able to delete.
I am currently trying with the following code:

Sub DeletethenSaveFile()

Dim Range As String
Set Proj = Sheets("Form").Range("$F$10")
DeleteFile "Blah Blah Blah" & Range("Proj") & "2.xls"
MsgBox "The requested file has been deleted"
ThisWorkbook.SaveCopyAs "Blah Blah Blah" & Range("Proj") & "2.xls"
MsgBox "Your file has been saved to it's new location"

End Sub

Please help! D

 

[halimnurikhwan]

Hi Damien,
May be you can try this :
Sub DeletethenSaveFile()
Dim Range As String, Addr as string, Proj as string
Proj = Sheets("Form").Range("$F$10")
Addr = "Blah Blah Blah" & Range(Proj) & "2.xls"
MsgBox "The requested file has been deleted"
ThisWorkbook.SaveCopyAs "Blah Blah Blah" & Range("Proj") & "2.xls"
Kill Addr 'To kill or delete old file after copying it
MsgBox "Your file has been saved to it's new location"
End Sub

 

[Guest]

Cheers, The Kill function works great, it also works with my code when I
knock out the Dim and Set and replace DeleteFile with Kill.

Kind Regards, D

 

[halimnurikhwan]

Hi Damien,
please note that Kill is not bring your file to Recycle bin, so you
can't restore them after Kill.

You can use API function call to send file to Recycle bin.

Thanks,

halim


Damien menuliskan:

 

[Guest]

Hi Halim,

Thanks a million, I need to delete permanently to stop multiple dupications
on our servers,

Kind Regards, D

 

[Ron de Bruin]

Another option is to use the VBA name function
See
http://www.rondebruin.nl/folder.htm