Hi JohnUK

Maybe you can use this tester to delete all excel files in a folder exept the newest file

Function NewestFile(Directory, FileSpec)
' John Walkenbach
' http://www.j-walk.com/ss/excel/tips/tip97.htm
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
Dim FileName As String
Dim MostRecentFile As String
Dim MostRecentDate As Date
If Right(Directory, 1) < "\" Then Directory = Directory & "\"
FileName = Dir(Directory & FileSpec, 0)
If FileName < "" Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(Directory & FileName)
Do While FileName < ""
If FileDateTime(Directory & FileName) MostRecentDate Then
MostRecentFile = FileName
MostRecentDate = FileDateTime(Directory & FileName)
End If
FileName = Dir
Loop
End If
NewestFile = MostRecentFile
End Function

Sub Kill_All_Old_Files_in_Folder()
Dim Folder As String
Dim str1 As String
Dim str2 As String
Folder = "c:\Data\"
str1 = NewestFile(Folder, "*.xls")
str2 = Folder & str1
If str1 < "" Then
Name str2 As Left(str2, Len(str2) - 4) & ".rdb"
On Error Resume Next
Kill Folder & "*.xls"
On Error GoTo 0
Name Left(str2, Len(str2) - 4) & ".rdb" As str2
End If
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl



"JohnUK" wrote in message ...
Hi Mike,
Thank you for your help.
I like the way the dialogue box opens so that you can pick the folder, but I
am afraid I need it to work without any intervention from the user apart from
pressing a button.
Let me tell you in more detail what it is I am after:

I have a workbook that creates a zipped copy of itself (Windows Zip)as it
closes down and saves to 'Backup' Folder in 'My Documents' (Date and Time
tagged)for Backup purposes. Example: Backup Thu 20 Jul 14 51 26.zip

I also have another function that backs up my Main Page data to an excel
file and saves as date and time into a different Backup file (Main Page
Backups) Example: Main Page Backup Thu 20 Jul 14 51 26.xls

As you can imagine I can end up with lots of backups.

I then need at a touch of a button a piece of code that goes to Backup
Folder within My Documents, deletes all the Zipped files apart from the
latest date and time, then goes into Main Page Backups folder to delete all
the excel files apart from the latest date and time.

I came across this piece of code from Ron De Bruin:
Kill "C:\TestFolder\*.xls"
But obviously it deletes all files which I don't want.

Regards
John

"crazybass2" wrote:

John,

NOTE: be very cautious when doing something like this. The file deleted
will not end up in the recycle bin.

I used the Excel standard for the first day of Jan 1, 1980. If your files
are earlier than that you may need to redefine dummydate. I used the
Workbook_Open event to allow the user to select a folder location, but you
may move all the code to one standard module procedure if you like.

In the ThisWorkbook Module put:

Private Sub Workbook_Open()
Dim foldername As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
foldername = .SelectedItems(1)
End With
ShowFolderList (foldername)
End Sub


AND in a standard module put:

Sub ShowFolderList(folderspec)
Dim fs, f1
Dim dummydate As Date
dummydate = "1/1/1980"
Set fs =
CreateObject("Scripting.FileSystemObject").GetFold er(folderspec).Files
For Each f1 In fs
If DateDiff("s", f1.datelastmodified, dummydate) < 0 Then
dummydate = f1.datelastmodified
Else: f1.Delete
End If
Next
For Each f1 In fs
If DateDiff("s", f1.datelastmodified, dummydate) 0 Then f1.Delete
Next
End Sub


Mike


"JohnUK" wrote:

Hi All,
I am after a piece of code that deletes all files within a folder with the
exception of the latest file created.
Again any help greatly appreciated
John