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

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

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 dont 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

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

Hi John

Try below, please.


Option Explicit
Option Base 1

'----------------------------------------------------------
' Procedure : KeepNewestFile
' Date : 20060722
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Together with Function
' 'NewestFile(Directory, FileSpec)'
' to Kill all ExtentionArr in a
' specified folder(s) except latest
' modifyed, (not CREATED).
' Note : Att.: Modifyed, (not CREATED).
'----------------------------------------------------------
'
Sub KeepNewestFile()
Dim CommonPath As String
Dim ExtentionArr As Variant
Dim x As Long

CommonPath = "D:\_a Desktop\__Delete"

ExtentionArr = Array(Array("", "*.xls"), _
Array("\zip", "*.zip"))

For x = 1 To UBound(ExtentionArr)
MsgBox NewestFile(CommonPath & ExtentionArr(x)(1), _
ExtentionArr(x)(2))
Next x
End Sub


'----------------------------------------------------------
' Procedure : NewestFile
' Date : 20060722
' Author : www.j-walk.com
' Modifyed by : Joergen Bondesen
' Purpose : Kill all FileSpec in a folder
' except latest modifyed, (not CREATED).
' Note : Att.: Modifyed, (not CREATED).
'----------------------------------------------------------
'
Function NewestFile(Directory, FileSpec)
' 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

'// kill
FileName = Dir(Directory & FileSpec, 0)
If FileName < "" Then
Do While FileName < ""
If FileName < NewestFile Then
Kill Directory & FileName
End If
FileName = Dir
Loop
End If

End Function


--
Best Regards
Joergen Bondesen


"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

Many thanks Ron €“ You done it again.
Below is the complete code I will be using:

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 Msg, Style, Title, Ctxt, Response, MyString
Msg = "Are you sure you want to delete all old backups?, Continue?"
Style = msoAlertButtonYesNoCancel + vbInformation + vbDefaultButton3
Title = "DELETE BACKUPS"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
MyString = "Yes"
proceed
Else
MyString = "No"
End If
End Sub
Sub proceed()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Folder As String
Dim str1 As String
Dim str2 As String
Folder = "C:\Documents and Settings\johns\Desktop\Test1"
str1 = NewestFile(Folder, "*.zip")
str2 = Folder & str1
If str1 < "" Then
Name str2 As Left(str2, Len(str2) - 4) & ".rdb"
On Error Resume Next
Kill Folder & "*.zip"
On Error GoTo 0
Name Left(str2, Len(str2) - 4) & ".rdb" As str2
End If
Folder = "C:\Documents and Settings\johns\Desktop\Test2"
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
MsgBox "All Old Backup Files Now Deleted" & vbNewLine & _
"", , "Operation Complete"
End Sub

Regards
John


"Ron de Bruin" wrote:

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

Hi Joergen Bondesen
I tried your code and it worked equally as well as Rons
Many thanks
Take care
Regards
John
PS - it amazes me how many different ways you can apply code to do the same
job


"Joergen Bondesen" wrote:

Hi John

Try below, please.


Option Explicit
Option Base 1

'----------------------------------------------------------
' Procedure : KeepNewestFile
' Date : 20060722
' Author : Joergen Bondesen
' Modifyed by :
' Purpose : Together with Function
' 'NewestFile(Directory, FileSpec)'
' to Kill all ExtentionArr in a
' specified folder(s) except latest
' modifyed, (not CREATED).
' Note : Att.: Modifyed, (not CREATED).
'----------------------------------------------------------
'
Sub KeepNewestFile()
Dim CommonPath As String
Dim ExtentionArr As Variant
Dim x As Long

CommonPath = "D:\_a Desktop\__Delete"

ExtentionArr = Array(Array("", "*.xls"), _
Array("\zip", "*.zip"))

For x = 1 To UBound(ExtentionArr)
MsgBox NewestFile(CommonPath & ExtentionArr(x)(1), _
ExtentionArr(x)(2))
Next x
End Sub


'----------------------------------------------------------
' Procedure : NewestFile
' Date : 20060722
' Author : www.j-walk.com
' Modifyed by : Joergen Bondesen
' Purpose : Kill all FileSpec in a folder
' except latest modifyed, (not CREATED).
' Note : Att.: Modifyed, (not CREATED).
'----------------------------------------------------------
'
Function NewestFile(Directory, FileSpec)
' 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

'// kill
FileName = Dir(Directory & FileSpec, 0)
If FileName < "" Then
Do While FileName < ""
If FileName < NewestFile Then
Kill Directory & FileName
End If
FileName = Dir
Loop
End If

End Function


--
Best Regards
Joergen Bondesen


"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

Add a \

Folder = "C:\Documents and Settings\johns\Desktop\Test1\"


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



"JohnUK" wrote in message ...
Many thanks Ron - You done it again.
Below is the complete code I will be using:

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 Msg, Style, Title, Ctxt, Response, MyString
Msg = "Are you sure you want to delete all old backups?, Continue?"
Style = msoAlertButtonYesNoCancel + vbInformation + vbDefaultButton3
Title = "DELETE BACKUPS"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
MyString = "Yes"
proceed
Else
MyString = "No"
End If
End Sub
Sub proceed()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Folder As String
Dim str1 As String
Dim str2 As String
Folder = "C:\Documents and Settings\johns\Desktop\Test1"
str1 = NewestFile(Folder, "*.zip")
str2 = Folder & str1
If str1 < "" Then
Name str2 As Left(str2, Len(str2) - 4) & ".rdb"
On Error Resume Next
Kill Folder & "*.zip"
On Error GoTo 0
Name Left(str2, Len(str2) - 4) & ".rdb" As str2
End If
Folder = "C:\Documents and Settings\johns\Desktop\Test2"
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
MsgBox "All Old Backup Files Now Deleted" & vbNewLine & _
"", , "Operation Complete"
End Sub

Regards
John


"Ron de Bruin" wrote:

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