[RESOLVED] Delete File

[Resource Dragon]

How do I use Visual Basic code to delete a file? The VBA documentation on “Delete” hasn’t helped me. The story behind my need for such code follows here:
I have a shared excel workbook (with a lot of VB code for forms &c) and I have written a routine (again, this routine lives in the shared workbook’s macros) that creates another workbook which is then populated with selected data from the original shared workbook. The new file is then saved to another part of a shared drive. The name of the new file depends upon the data that is chosen to populate it.
To do this, when I first create the new workbook, I call it “Interim Cert” and it is saved in the same folder as the shared file. When the Interim Cert is fully populated, the code then picks up the file name that is to be used and works out where the file is to be saved – there are 12 possible folders, depending upon the month. I do this using the SaveAs command.
My problem is that I’ve got a file called Interim Cert.xls to dispose of. The following code, based on Koolsid’s answer to the question, “How do I delete a file while it is open?” is satisfactory if I am only creating one new file from the original shared file but doesn’t work if I am creating half a dozen.

Workbooks.Open ("G:\Resources and Publications\Finance 09 10\Interim Cert.xls")
Workbooks("Interim Cert.xls").Activate
With ActiveWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
Workbooks("Interim Cert.xls").Close
End With

So, can someone please tell me how to delete the file? Alternatively, can someone tell me how to rename and move the file? (The whole problem arises because the original file is shared. When I started writing the code, I simply added another worksheet, then moved it into its own workbook.)
Thank you.

[Siddharth Rout]

You can delete files using the API SHFileOperation. Search the forum and you will find plenty of examples on this API.

[Resource Dragon]

G’day Koolsid. I hadn’t heard of API SHFileOperation before, which was why I hadn’t looked for it. I followed up your suggestion and came across http://www.techbookreport.com/tutorials/fso1.html which explained the concept in easy steps for a beginner.

I’ve got code for a deletion working in a practice file. I haven’t had a chance to run the code in the live file yet but I think it should work. Thanks for the suggestion.

Cheers

Resource Dragon

[westconn1]

you can't really delete the workbook while it is open in excel, you need to close the workbook first, of course if the code is n that workbook it stops running

fname = activeworkbook.fullname
activeworkbook.close false
kill fname