Kill Function by author

[Gordon]

Hi.

I'm handing back my laptop to a company having worked on secondment for them
for a year. In this time I've created hundreds of excel files that sit
mingled with 100's from the company. They have no designated home and sit
randomly across the hard drive. Basically, I want to delete all of the files
that I created and leave the files behind that are generic to the company. I
know the kill function can work on set locations, but is there a way to
delete files based on the value in the author/properties field? sounds tricky.

THanks in advance of a seriously clever answer!

GC

 

[Bob Phillips]

This function can get the uthor property of an open workbook, which you can
then use the path of to kill


'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


use as

If DocProps("author") = "me" Then

Kill Activeworkbook.Fullname
ActiveWorkbook.Close SaveChanges:=False
End If

 

[Gordon]

Hi Bob...

If my user name is say Boffin, can you package that code so that I can just
run it once from an excel sheet and all excel files with the author name
Boffin are deleted on my laptop, regardless of location or path?

Thanks so far... we're getting there.

Cheers

 

[Bob Phillips]

I would think this will be incredibly slow, going through every file on the
system

Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

If Folder.Name = "Windows" Then Exit Sub
On Error GoTo exit_sub
For Each fldr In Folder.Subfolders

selectFiles fldr.Path
Next fldr

On Error GoTo 0
For Each file In Folder.Files

If file.Type Like "*Microsoft*Excel*Worksheet*" Then

Workbooks.Open Filename:=file.Path
If DocProps("author") = "Boffin" Then

Kill ActiveWorkbook.FullName
End If
ActiveWorkbook.Close SaveChanges:=False
End If
Next file
exit_sub:
End Sub


'-----------------------------------------------------------------
Function DocProps(prop As String)
'-----------------------------------------------------------------
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function