Excel

Remove the Old File

Ease of Use

Easy

Version tested with

2000, 2002, 2003 

Submitted by:

Justinlabenne

Description:

After saving the workbook with a new name, the previous file is destroyed. 

Discussion:

If you have a workbook that you save under a different file name when it is updated, or to reflect a current date of update, this code will allow you to save the file under a new name you choose, and then remove the old file from the disk. This saves time if you are going back and manually deleting previous files that you don't need anymore because a newer version is available. 

Code:

instructions for use

			

Option Explicit Sub KillPreviousFile() Dim szMsgResponse As String ' Get the name of this workbook with out the .xls Dim szDefaultName As String szDefaultName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) StartAgain: ' Use an input box to obtain the new file name: Dim szNewBookName As String szNewBookName = InputBox("Please enter a name for the new file" & _ vbNewLine & _ "It will be saved in the same directory as the original" & vbNewLine & _ vbNewLine & _ "Valid file-names cannot include these characters" & vbNewLine & _ "< > \ / * ? | : ; """, , szDefaultName) ' If a name has been specified: If szNewBookName <> Empty Then ' Suppress messages Application.DisplayAlerts = False ' Determine old workbooks path and name and store for later use Dim szOldBook As String szOldBook = ThisWorkbook.FullName ' Create a valid path for our new file, same directory as this file Dim szThisPath As String szThisPath = ThisWorkbook.Path & "\" ' Build our new file name Dim szNewFileName As String szNewFileName = szThisPath & szNewBookName & ".xls" ' If the user typed in the same name as the original, we have some options ' we can present, by either starting the procedure over, saving the file, ' or canceling the procedure entirely If szNewFileName = szOldBook Then ' Variable szMsgResponse holde the msgbox button press: szMsgResponse = MsgBox("The new file name is the same as the original" & _ vbNewLine & "Would you like to save now, try again, or cancel?", 19) ' Proceed based on the selected option Select Case szMsgResponse Case 2 Exit Sub Case 7 GoTo StartAgain Case 6 ThisWorkbook.Save Exit Sub End Select End If ' If we are valid, save this file under the new name: On Error GoTo ExitProc ThisWorkbook.SaveAs szNewFileName, xlWorkbookNormal ' Then remove the old workbook we just were using Kill szOldBook Else ' if nothing was given in the input box, just exit. Exit Sub End If ExitProc: Application.DisplayAlerts = True Exit Sub InvalidName: MsgBox Err.Description GoTo ExitProc End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Press Alt + F11 to open the Visual Basic Editor (VBE)
  4. Select INSERT > MODULE from the menubar
  5. Paste code into the right pane
  6. Press Alt+Q to return to Excel
  7. Save workbook before any other changes
 

Test the code:

  1. Go to TOOLS > MACRO > MACROS
  2. When the dialog appears, select {KillPreviousFile}
  3. Press Run
 

Sample File:

KillOldFile.zip 9.59KB 

Approved by mdmackillop


This entry has been viewed 155 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express