? for Ron de Bruin

[Chet]

Ron thanks for your help with deleting all VBE from a
list of file in a specific folder on computer your code
was awesome!!!

Now I want to kind of do the same thing except I want to
delete files that are older than a specific date. All
files are dated in cell C8. Basically - if date is older
than _ then delete file from folder...Is there a way to
do this?

I would just sort my list by date in the file folder but
when I updated the list earlier I change the modified
date to today and can't sort them by date anymore.

Thanks
Chet

 

[Ron de Bruin]

Hi Chet

If C8 in the first sheet of each workbook have a real date then
try this to delete files that are older then 10 days

See the vba help for the Isdate function.to check the cell value is really a date


Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
'your code
If mybook.Sheets(1).Range("C8") < Now() - 10 Then
mybook.Close False
MsgBox "use Kill FNames to delete the file" & FNames
Else
mybook.Close False
End If
FNames = Dir()
Loop

 

[Chet]

Ron your code below does do everything I want except
actually delete the file from the folder. From what I can
tell Kill Fname is not working. Here is my code I
modified to make this cycle throught.

Sub Remove_Files_Older_Than()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Documents and Settings\120377\My
Documents\Sent Faxes\"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
If mybook.Sheets("Fax Cover").Range("C8") < Now
() - 180 Then
mybook.Close False
MsgBox "use Kill FNames to delete the
file" & FNames
Else
mybook.Close False
End If
FNames = Dir()
Loop

End Sub

The date in cell C8 is listed as 3/14/2004, so I believe
this is a true date you were looking for.

Any thoughts
Chet

-----Original Message-----
Hi Chet

If C8 in the first sheet of each workbook have a real date then
try this to delete files that are older then 10 days

See the vba help for the Isdate function.to check the cell value is really a date


Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
'your code
If mybook.Sheets(1).Range("C8") < Now() - 10 Then
mybook.Close False
MsgBox "use Kill FNames to delete the file" & FNames
Else
mybook.Close False
End If
FNames = Dir()
Loop


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


"Chet" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Chet

Change the Msgbox line to this
Kill FNames

The Msgbox is only for testing

 

[Chet]

Thanks Ron... It amazing how simple things can be if you
just look at them correctly. I did try to add a line Kill
Fnames to your code, but it gave me an error, so I didn't
try it anymore.

Thanks again
Chet

-----Original Message-----
Hi Chet

Change the Msgbox line to this
Kill FNames

The Msgbox is only for testing


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


"Chet" <[email protected]> wrote in

message news:[email protected]...