Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Overwriting a file with a macro (Excel 2000)

    Dear All

    I've got a macro that creates a spreadhseet from provided data, the macro creates the same spreadsheet every time ti runs (daily usually). I'm trying to avoid the user having to click the OK button every time it replaces 'yesterday's' file. Is there a way of moving, deleting or generally getting rid of the old file??

    I've seen the DeleteFile method, but can't make out the bit about the FileSystemIObject. How should i be using this, if at all?

    Thanks

    Ian

  2. #2
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Overwriting a file with a macro (Excel 2000)

    Thanks Hans

    I'll use the 'Kill' option (love a bit of violence me [img]/forums/images/smilies/smile.gif[/img] ). For whatever reason the Excel default on the pop up is 'No', so I couldn't use the option about turning warnings off.

    Thanks

    Ian

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Overwriting a file with a macro (Excel 2000)

    The good old Kill instruction from pre-Visual Basic days still works:

    ' No error if file doesn't exist
    On Error Resume Next
    ' Delete file
    Kill "Bill.xls"
    ' Restore error handling
    On Error GoTo MyErrorHandler

    Alternatively, you can insert Application.DisplayAlerts = False before, and Application.DisplayAlerts = True after the Save As instruction. Excel will automatically use the default option in message boxes - in this case Yes for the question whether it is OK to overwrite the existing file.

Posting Permissions

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