search results into excel file 2

[DestroyerNr1]

Hi,

I want to search for files bigger than 10MB or equal on a terminal server. All the files found (also in the subsubsub... folders) must be put in an excel file.
The info of the files i look for is name, size, type, last modified and last accessed.
I found a script on that maybe will do the work but it doesn't put it in a xls file and the messageboxes must go away... the script must be modified.

here it is:

Dim FSO, WSH, objDirectory, objFile, TheFiles

Set FSO = CreateObject("Scripting.FileSystemObject")
Set WSH = CreateObject("Wscript.Shell")


Set objDirectory = FSO.GetFolder("c:\")
Set TheFiles = objDirectory.Files

Parentfolder = mid(fso.GetFolder(objDirectory),InstrRev(fso.GetFolder(objDirectory),"\",-1,0)+1,len(fso.GetFolder(objDirectory)) - InstrRev(fso.GetFolder(objDirectory),"\",-1,0))
objextension = InputBox("Enter extension" & vbcrlf & vbcrlf & ".*" & vbcrlf & "mp3" & vbcrlf & "bmp" & vbcrlf & "exe")


If FSO.FileExists ("c:\FileNames " & Parentfolder & ".txt") then
FSO.DeleteFile "c:\FileNames " & Parentfolder & ".txt"
End If

Set txtFileName = FSO.CreateTextFile ("c:\FileNames " & Parentfolder & ".doc",TRUE)
Set txtFileName = Nothing
Set txtFileName = FSpenTextFile("c:\FileNames " & Parentfolder & ".doc", 8)

WorkWithSubFolders objDirectory

txtFileName.Close


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub WorkWithSubFolders(objDirectory)
Dim MoreFolders, TempFolder

ListFilesWithExtension objDirectory
Set MoreFolders = objDirectory.SubFolders

For Each TempFolder In MoreFolders
WorkWithSubFolders TempFolder
Next

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ListFilesWithExtension(objDirectory)
Dim TheFiles

Set TheFiles = objDirectory.Files
For Each objFile in TheFiles
strExt = fso.GetExtensionName(objFile.Path)
If (strExt = objextension) Then
txtFileName.writeline objFile.Path & vbtab & objFile.Size & vbtab & objFile.DateCreated & vbtab & objFile.DateLastModified
end if
Next
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Hope someone can help me out here!

 

[DestroyerNr1]

Sorry but I have one 'last' question.
I wanna know who are the owners of the files. But the file object doesn't have an owner attribute.
I've found a script on the net that does the job but I want to put it in this script. The only problem is where do I put all those lines and how do I put it in the excel file also?

I'm not that good in scripting, should like to take a course but I aint got the time

Here is that script I've found

Option Explicit
Dim folder

folder = "c:\"
getFolder(folder)

Function getFolder(root)
Dim fso, folders, folder, file, files
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(root) Then
WScript.Echo "Folder: " & root & vbTab & "Owner: " & getOwner(root)
For Each file In fso.GetFolder(root).Files
WScript.Echo "File: " & file & vbTab & "Owner: " & getOwner(fso.GetAbsolutePathName(file))
Next
For Each folder In fso.getFolder(root).SubFolders
getFolder(fso.GetAbsolutePathName(folder))
Next
Else
WScript.Echo "Folder doesn't exist: " & root
Exit Function
End If
End Function

Function getOwner(object)
Dim su, sd
Set su = CreateObject("ADsSecurityUtility")
Set sd = su.GetSecurityDescriptor(object, 1, 1)
getOwner = sd.Owner 
End Function

 

[tsuji]

You do it like this.
[tt]
Sub ListFilesWithExtension(objDirectory)
Dim TheFiles
on error resume next
Set TheFiles = objDirectory.Files
if err.number<>0 then err.clear : exit sub
For Each objFile in TheFiles
if err.number<>0 then err.clear : exit sub
if objFile.size\(1024*1024) >= 10 then
Row = Row+1
objXL.Cells(Row,Column).Value = objDirectory
objXL.Cells(Row,Column+1).Value = objFile.Name
objXL.Cells(Row,Column+2).Value = objFile.Size
err.clear
objXL.Cells(Row,Column+3).Value = objFile.DateCreated
if err.number<>0 then
objXL.Cells(Row,Column+3).Value = "na"
err.clear
end if
objXL.Cells(Row,Column+4).Value = objFile.DateLastModified
objXL.Cells(Row,Column[blue]+5[/blue]).Value = getOwner(objFile.path)
End If
Next
on error goto 0
End Sub
[/tt]
And you make the header row adding owner.
[tt]
objXL.Cells(1,Column).Value = "Parent Folder"
objXL.Cells(1,Column+1).Value = "File Name"
objXL.Cells(1,Column+2).Value = "File Size"
objXL.Cells(1,Column+3).Value = "Date Created"
objXL.Cells(1,Column+4).Value = "Date Last Modified"
objXL.Cells(1,Column+5).Value = "Owner"
[/tt]
And cut and past the function getOwner(object). Note: though it is called object as the parameter, it is a simple string type string.

- tsuji

 

[DestroyerNr1]

THANK YOU!!!!
The script is finished!

Many thanks for your help!

 

[DestroyerNr1]

Sorry but I have another question.
I wanna search on size and date now.

Dim FSO, WSH, objDirectory, objFile, TheFiles
'
Set FSO = CreateObject("Scripting.FileSystemObject")
Set WSH = CreateObject("Wscript.Shell")
Set objDirectory = FSO.GetFolder(InputBox("Enter Starting Folder"))
Set TheFiles = objDirectory.Files
'
' Create and Set up Excel Objects
Column = 1
Row = 1
RowErr = 1
Set objXL = WScript.CreateObject("Excel.Application")
'
objXL.Workbooks.Add
objXL.Cells(1,Column).Value = "Parent Folder"
objXL.Cells(1,Column+1).Value = "File Name"
objXL.Cells(1,Column+2).Value = "File Size"
objXL.Cells(1,Column+3).Value = "Date Created"
objXL.Cells(1,Column+4).Value = "Date Last Modified"
objXL.Cells(1,Column+5).Value = "Date Last Accessed"
objXL.Cells(1,Column+6).Value = "Owner"
objXL.Visible = True
'
WorkWithSubFolders objDirectory
'
Sub WorkWithSubFolders(objDirectory)
     Dim MoreFolders, TempFolder
     ListFilesWithExtension objDirectory
     Set MoreFolders = objDirectory.SubFolders
     on error resume next
     For Each TempFolder In MoreFolders
          WorkWithSubFolders TempFolder
     Next
End Sub
       
'
'ListFilesWithExtension objDirectory
'
Sub ListFilesWithExtension(objDirectory)
Dim TheFiles
     on error resume next
      Set TheFiles = objDirectory.Files
     if err.number<>0 then err.clear : exit sub
     For Each objFile in TheFiles
       if err.number<>0 then err.clear : exit sub
       If objFile.Size\ (1024*1024) >= 10 then
  [COLOR=red]If objFile.DateLastModified\ (yyyymmdd) <= 20010101000000.000000+000 then[/color]
       
      Row = Row+1
      objXL.Cells(Row,Column).Value = objDirectory
      objXL.Cells(Row,Column+1).Value = objFile.Name
      objXL.Cells(Row,Column+2).Value = objFile.Size
     err.clear
      objXL.Cells(Row,Column+3).Value = objFile.DateCreated
                    if err.number<>0 then
      objXL.Cells(Row,Column+3).Value = "na"
                       err.clear
                    End If
 objXL.Cells(Row,Column+4).Value = objFile.DateLastModified
 objXL.Cells(Row,Column+5).Value = objFile.DateLastAccessed
 objXL.Cells(Row,Column+6).Value = getOwner(objFile.path)
             End if
     Next
     on error goto 0
End Sub
Function getOwner(object)
Dim su, sd
Set su = CreateObject("ADsSecurityUtility")
Set sd = su.GetSecurityDescriptor(object, 1, 1)
getOwner = sd.Owner 
end function
'
MsgBox("Hell Yeah!")
WScript.Quit

The highlighted line should search for files older then a specific date.
But how the hell do you do that?

 

[PHV]

Something like this ?
If objFile.DateLastModified <= #2001-01-01# Then
Or this ?
If objFile.DateLastModified <= DateSerial(2001, 1, 1) Then

Note: don't forget the corresponding End If
Hint: Indent properly your code

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DestroyerNr1]

That's exactly what I want!
I tried it and I think my End If isn't on the right place or so, but the script doesn't look for files bigger then 10MB and older then 20010101. It searches only for the date and when put it somewhere else, it searches only on size and I want both.

Sub ListFilesWithExtension(objDirectory)
Dim TheFiles
     on error resume next
      Set TheFiles = objDirectory.Files
     if err.number<>0 then err.clear : exit sub
     For Each objFile in TheFiles
       if err.number<>0 then err.clear : exit sub
       [COLOR=red]If objFile.Size\ (1024*1024) >= 10 then
       end if 
       If objFile.DateCreated <= DateSerial(2001, 1, 1) then[/color]
       
       
       
       
                    Row = Row+1
                    objXL.Cells(Row,Column).Value = objDirectory
                    objXL.Cells(Row,Column+1).Value = objFile.Name
                    objXL.Cells(Row,Column+2).Value = objFile.Size
                       err.clear
                    objXL.Cells(Row,Column+3).Value = objFile.DateCreated
                    if err.number<>0 then
                    objXL.Cells(Row,Column+3).Value = "na"
                       err.clear
                    End If
                    objXL.Cells(Row,Column+4).Value = objFile.DateLastModified
                    objXL.Cells(Row,Column+5).Value = objFile.DateLastAccessed
                    objXL.Cells(Row,Column+6).Value = getOwner(objFile.path)
             End if
        
     Next
     on error goto 0
End Sub
Function getOwner(object)
Dim su, sd
Set su = CreateObject("ADsSecurityUtility")
Set sd = su.GetSecurityDescriptor(object, 1, 1)
getOwner = sd.Owner 
end function
'
MsgBox("Hell Yeah!")
WScript.Quit

 

[PHV]

Either:
For Each objFile in TheFiles
if err.number<>0 then err.clear : exit sub
If objFile.Size \ (1024*1024) >= 10 Then
If objFile.DateCreated <= DateSerial(2001, 1, 1) Then
Row = Row+1
objXL.Cells(Row,Column).Value = objDirectory
...
End if
End If
Next
Or:
For Each objFile in TheFiles
if err.number<>0 then err.clear : exit sub
If objFile.Size \ (1024*1024) >= 10 _
And objFile.DateCreated <= DateSerial(2001, 1, 1) Then
Row = Row+1
objXL.Cells(Row,Column).Value = objDirectory
...
End if
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DestroyerNr1]

Hmmm, still doesn't work. Now it doesn't put anything in the excel file. I tried both options.
Any idea?

 

[PHV]

Have you really files older than 20010101 and bigger than 10 Mb in your starting folder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

BTW, for some files the DateCreated method raise an error ...
You may try this:
For Each objFile in TheFiles
If objFile.Size \ (1024*1024) >= 10 Then
If objFile.DateCreated <= DateSerial(2001, 1, 1) Then
Row = Row+1
objXL.Cells(Row,Column).Value = objDirectory
...
End if
Err.Clear
End If
Next


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DestroyerNr1]

Ok, sorry about that. My (personal;-) ) memory failed for a few minutes....

It works perfectly!
Put the err.clear line in to it too.

Thanks!!!