Excel 2010 VBA: language to delete "temp.xls" file

[axsc]

I have a two-part payroll process where employee data is exported from an Access utility to an Excel file, whereupon the user runs an Excel macro of mine to put the data into the format required by the mainframe payroll program.

In the newest version -- and the company has just gone to Office 2010 -- the Access programmer creates a temp file in Excel and uses that as a base to create a second file opened for the user. The user runs my macro, which assembles the data the mainframe program wants and copies it to the clipboard, ready to be pasted into the mainframe program. Then the user chooses the next employee and the process is repeated.

Excel 2010, however, is now stopping in its tracks with the message that the "temp.xls" file exists and does the user want to overwrite it?

I think the most practical approach is for me to add a line to my macro to delete "temp.xls" once it's no longer needed, but I can't find the code to do it. Since the process uses the same file name over and over, I should be able to keep the whole process invisible to the user.

Any suggestions?

Many thanks,

Ann

[Andrew Lockton]

Have a look at this link - does that help
http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm

[axsc]

First, thank you, Andrew; the method worked perfectly. The temp file is gone for good.

Second, I now have a related query. Once the temp file was gone, I had the bright idea of deleting the current file on closing. A suitable KillFile closed and deleted the current file; Excel's Recent File list doesn't bring it up and it isn't in the folder where it was created.

If I then close Excel, I can run the whole process again where Access creates a new Excel file of the same name and opens it smoothly.

If I don't close Excel, running the Access procedure causes Excel to tell me that a file by that name -- the former current file -- exists and do I want to write over it?

I presume this means that the cache isn't cleared. Can I clear the cache of a current file/filename through code in the macro?

Many thanks,

Ann

[Andrew Lockton]

This sounds like the application has locked the file and won't release it until the app closes. I sometimes see this if I open Word by double clicking a file and then later close the file but leave Word open. If I then try to delete the file, I get a message that the file is in use.

I've never tried to find a code solution to this problem - I just return to Word and close it before trying again to delete the file.