Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,515
    Thanks
    3
    Thanked 143 Times in 136 Posts
    Have a look at this link - does that help
    http://word.mvps.org/faqs/macrosvba/DeleteFiles.htm
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,515
    Thanks
    3
    Thanked 143 Times in 136 Posts
    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.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •