Overwriting a file with a macro (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans

I’ll use the ‘Kill’ option (love a bit of violence me ). 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

Reply | Quote

Thanks Hans

I’ll use the ‘Kill’ option (love a bit of violence me ). 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

Reply | Quote

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.

Reply | Quote