View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_3_] Bob Phillips[_3_] is offline
external usenet poster
 
Posts: 2,420
Default Kill Function by author

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




--
__________________________________
HTH

Bob

"Gordon" wrote in message
...
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" wrote:

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

--
__________________________________
HTH

Bob

"Gordon" wrote in message
...
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