Script to list all files and properties from directory

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

Script to list all files and properties from directory

Logged in as: Guest

arrSession:exec spGetSession 2,2,56218

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> Script to list all files and properties from directory
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

Script to list all files and properties from directory - 1/31/2008 2:52:40 PM

abracetti

Posts: 2
Score: 0
Joined: 1/31/2008
Status: offline

Hi Guys,

I'm trying to create a vbs script to run in a folder of a file server to get some details of all the files in that folder, let's say I have 100 users storing files on this server and they have each one access to only one personal folder on the server, but as USERS they do not worry about saving sapce on the server and do not cleanup their files so the server keeps filling up. I wanted to via script create an excel spreadsheet to list all files with some its properties like: Name, Size, Type, Date Created, Date Modified, Last Accessed and Path.
With this script I'm trying to list files that have not been accessed for a long time and clear them up , since we have a backup of all this.
I already have a script to list the files on the root of the folder but i also wanted to know what is inside the subfolders......
I haven't used much scripting before but nevertheless I was trying to create this script by myself but was getting to the point where i would have to recall the subroutine too many times and was afraid of allocating too much memory for this script and maybe crash the server , not speaking of running out of rows within the excel file........
The basic script I used is the one below but it doesn't go trough subfolders.......

Can someone shed some light please???

Dim intRow
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
objExcel.Cells(1,1).Value = "Name"
objExcel.Cells(1,2).Value = "Size"
objExcel.Cells(1,3).Value = "Type"
objExcel.Cells(1,4).Value = "Date Created"
objExcel.Cells(1,5).Value = "Date Modified"
objExcel.Cells(1,6).Value = "Last Accessed"

For Each strFileName in objFolder.Items
For i = 0 to 6

objExcel.Cells(intRow,i+1).Value = objFolder.GetDetailsOf(strFileName, i)

Next

intRow = intRow + 1
Next

Post #: 1

RE: Script to list all files and properties from directory - 1/31/2008 11:01:49 PM

ehvbs

Posts: 2173
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline

Hi abracetti,

as I wouldn't touch the Shell.Application with a long pole (bad design/implementation/documentation),
my code uses the FileSystemObject:

Dim oFS       : Set oFS      = CreateObject( "Scripting.FileSystemObject" )
 Dim sSDir     : sSDir        = oFS.GetAbsolutePathName( ".\zaptmp\users"  )
 Dim sXlsFSpec : sXlsFSpec    = oFS.GetAbsolutePathName( ".\dirlist.xls"   )
 Dim dicCargo  : Set dicCargo = CreateObject( "Scripting.Dictionary" )
 Dim oExcel    : Set oExcel   = CreateObject( "Excel.Application" )
 Dim oWBook    : Set oWBook   = oExcel.Workbooks.Add
 Dim oWSheet   : Set oWSheet  = oWBook.Sheets( 1 )
 Dim aProps    : aProps       = Array(   "Name", "Size", "Type" _
                                       , "DateCreated", "DateLastModified" _
                                       , "DateLastAccessed" _
                                     )
 Dim nRow      : nRow         = 1
 Dim nCol      : nCol         = 1

Dim sCol
 For Each sCol In aProps
     oExcel.Cells( nRow, nCol ).Value = sCol
     nCol = nCol + 1
 Next
 sCol = Chr( 64 + nCol )
 With oWSheet.Range( "A" & nRow & ":" & sCol & nRow )
   With .Font
       .Name = "Arial"
       .FontStyle = "Fett"
       .Size = 10
   End With
 End With

Set dicCargo( "fncFile"  ) = GetRef( "FileFncExcelFS" )
 Set dicCargo( "fncDir"   ) = GetRef( "DirFncExcelFS" )
 Set dicCargo( "oSFolder" ) = Nothing
 Set dicCargo( "oWSheet"  ) = oWSheet
     dicCargo( "aProps"   ) = aProps
     dicCargo( "nRow"     ) = nRow + 1
     dicCargo( "sCol"     ) = sCol

recurseDirFS oFS.GetFolder( sSDir ), dicCargo, 0

oWSheet.Cells.Columns.AutoFit
 oExcel.DisplayAlerts = False
 oWBook.SaveAs sXlsFSpec
 oWBook.Close
 oExcel.Quit
 WScript.Quit 0

Function FileFncExcelFS( oFile, dicCargo, nLevel )
 Dim nCol : nCol = 0
 Dim sProp
 For Each sProp In dicCargo( "aProps" )
     nCol = nCol + 1
     dicCargo( "oWSheet" ).Cells( dicCargo( "nRow" ), nCol ).Value = EVal( "oFile." & sProp )
 Next
 dicCargo( "nRow"    ) = dicCargo( "nRow" ) + 1
End Function

Function DirFncExcelFS( oFolder, dicCargo, nLevel )
 Const xlCenter = -4108
 dicCargo( "nRow"    ) = dicCargo( "nRow" ) + 1
 dicCargo( "oWSheet" ).Cells( dicCargo( "nRow" ), 2 ).Value = nLevel & " " & oFolder.Path
 With dicCargo( "oWSheet" ).Range( "A" & dicCargo( "nRow" ) & ":" & dicCargo( "sCol" ) & dicCargo( "nRow" ) )
   .HorizontalAlignment = xlCenter
   .MergeCells = True
   With .Font
     .Name = "Arial"
     .FontStyle = "Fett"
     .Size = 10
   End With
 End With
 dicCargo( "nRow"    ) = dicCargo( "nRow" ) + 1
End Function

Sub recurseDirFS( oFolder, dicCargo, nLevel )
 Dim vRet : vRet = dicCargo( "fncDir" )( oFolder, dicCargo, nLevel )
 Dim oItem
 For Each oItem In oFolder.Files
     vRet = dicCargo( "fncFile" )( oItem, dicCargo, nLevel )
 Next
 Dim nCnt
 nCnt = oFolder.SubFolders.Count
 If 0 < nCnt Then
    For Each oItem In oFolder.SubFolders
        recurseDirFS oItem, dicCargo, nLevel + 1
    Next
 End If
End Sub

You'll find some discussion about recursive dir walking here

http://www.visualbasicscript.com/fb.aspx?m=55650

Feel free to ask, if you need further explanations.

Good luck!

ehvbs

(in reply to abracetti)