Script to export all folders and files to text file

[tavie]

This one is driving me insane.....

I want a script to read in a variable (Drive Letter)
the script will then read the root and export all the file names to an Access DB including filesize and mod date stamp. It will then do the same for all subfolders and files within them. I can get part of it working but for some reason it will not allow me to include the datestamps.

Here is my script....

'******************************************************
' *************************************************************************
Const ForAppending = 8
Dim fs,folder,wshShell,msg

Set fs = CreateObject("Scripting.FileSystemObject")
Set wshshell = CreateObject("WScript.Shell")

'10

folder="L:\" 'Provide any path here you choose.

Set folder = fs.getfolder(folder)
' **********************************************************************
' Establish Database Connection
' **********************************************************************
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Server_Info;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM SVR_File_Info" , objConnection, _
adOpenStatic, adLockOptimistic
'25

Public FolderCount,FileCount
objRecordset.AddNew
objRecordset("Folder") = Folder 'Inserts Root PAth
objRecordset("SizeInKB") = FormatNumber((Folder.size/1024), 0)
wscript.echo Folder.DateLastModified
'objRecordset("ModDate") = Folder.DateLastModified
'objRecordset("CreateDate") = Folder.DateCreated
objRecordset.Update
ListfoldersandFiles(Folder)
FolderCount=0
ListfoldersandFilesc(Folder)
'36
'objTextFile.WriteLine "******* There are " & FolderCount & " folders and " & FileCount & " files in Folder: " & Folder & " ***************"
'objTextFile.Close

msgbox "Complete"
objRecordset.Close
objConnection.Close
'44
' *****************************************************************
' Sub Routines for File and Folder Recursion
'
' *****************************************************************
Sub ListfoldersandFiles(folder)




For Each File In folder.files
FileCount=FileCount+1 'Filecount Start
objRecordset.AddNew
objRecordset("Folder") = folder
objRecordset("FileName") = File.name ' Inserts Filename within Folder
'54
objRecordset("SizeInKB") = FormatNumber((file.Size/1024), 0)
objRecordset.Update
Next
'objRecordset("FilesinFolder") = folder & ": " & FileCount
FileCount=0
For Each subfolder In folder.subfolders
objRecordset.AddNew
objRecordset("Folder") = Subfolder
objRecordset("SizeInKB") = FormatNumber((Subfolder.Size/1024), 0)
objRecordset.Update
Foldercount=FolderCount+1 'FileCount Continue
'66
ListFoldersandfiles(Subfolder)
Next
End Sub

' ***********************************************************************
' Sub Routines for File and Folder Count
'
' ***********************************************************************
Sub ListfoldersandFilesc(folder)
'76
For Each File In folder.files
FileCount=FileCount+1
'wscript.echo File.name
Next

For Each subfolder In folder.subfolders
'wscript.echo Subfolder
Foldercount=FolderCount+1
ListFoldersandfilesc(Subfolder)
'86
Next
End Sub

' *****************************************************

 

[markdmac]

here is a simple example that should help you get it working:

Dim fso 
Dim oFolder
Dim oFile

Path = "C:\"

Set fso = createobject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(Path)
  
For Each oFile In oFolder.files
	WScript.Echo "File:" & oFile.Name & " Last Modified:" & oFile.DateLastModified & " Created:" & oFile.DateCreated
Next

I hope you find this post helpful. Please let me know if it was.

Regards,

Mark

 

[tavie]

Thanks Mark.

However this only looks at the files in the root folder, not all subfolder and files within.

 

[tsuji]

tavie,

The main problem of your script is that you mix up a lot of object and string and naming of folder object. And I think once you clear it up, date/time stamp is not the problem and I would guess at its present state, it won't run without error even you commented out all the date/time assignment lines.

These are the lines you have to change. You adapt to your liking in naming convention later. Add dim etc if you have option explicit.
[tt]
'...
[red]s[/red]folder="L:\" 'Provide any path here you choose.
Set folder = fs.getfolder([red]s[/red]folder)
'...
objRecordset("Folder") = Folder[red].path[/red] 'Inserts Root PAth
'...
Sub ListfoldersandFiles(folder)
'...
objRecordset("Folder") = folder[red].path[/red]
'...
objRecordset("Folder") = Subfolder[red].path[/red]
'...
[/tt]
(The format of the fields may have minor impact. Even if they are of type text, you won't have problem to assign a datetime variable to it. It would convert automatically.)

regards - tsuji

 

[tavie]

Well basically I am having a problem with recursivly listing out all the files and folders in a directory...It needs to include all subfolders and files within those subfolders...Anyone have a script that does this ????

 

[PHV]

Have you tried to search this forum for recursive fso ?

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

 

[tsuji]

Like it or not, I implement the script with my corrections mentioned, and it walks over the complete branch. May be you really didn't try at all. Those corrections are _not_ unrelated to recursion. You write an folder object to a db and the engine encounters multi-operation blah error.

- tsuji

 

[tsuji]

This is an elaborate salvage from the code which should work for folder root or not.

sfolder="L:\"	'root may take a long while to complete eg c:\

Set fs = CreateObject("Scripting.FileSystemObject")
Set folder = fs.getfolder(sfolder)

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Server_Info;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM SVR_File_Info" , objConnection, adOpenStatic, adLockOptimistic

Public FolderCount,FileCount
objRecordset.AddNew
objRecordset("Folder") = Folder.path
objRecordset("SizeInKB") =  FormatNumber((getfoldersize(folder)/1024), 0) 
if not folder.isrootfolder then
    objRecordset("ModDate") = Folder.DateLastModified
    objRecordset("CreateDate") = Folder.DateCreated
end if
objRecordset.Update
ListfoldersandFiles(Folder)
FolderCount=0
ListfoldersandFilesc(Folder)
'msgbox "Folder count : " & foldercount
msgbox "Complete"
objRecordset.Close
objConnection.Close

' Sub Routines for File and Folder Recursion
Sub ListfoldersandFiles(ofolder)
    For Each File In ofolder.files
        FileCount=FileCount+1      'Filecount Start
        objRecordset.AddNew
        objRecordset("Folder") = ofolder.path
        objRecordset("FileName") = File.name
        objRecordset("SizeInKB") = FormatNumber((file.Size/1024), 0)
        objRecordset.Update
    Next
    'objRecordset("FilesinFolder") =  folder.path & ": " & FileCount
    FileCount=0
    For Each subfolder In ofolder.subfolders
        objRecordset.AddNew
        objRecordset("Folder") = Subfolder.path
        objRecordset("SizeInKB") = FormatNumber((Subfolder.Size/1024), 0) 
        objRecordset("ModDate") = subFolder.DateLastModified
        objRecordset("CreateDate") = subFolder.DateCreated
        objRecordset.Update
        Foldercount=FolderCount+1 'FileCount Continue
        ListFoldersandfiles(Subfolder)
    Next
End Sub

' Sub Routines for File and Folder Count
Sub ListfoldersandFilesc(ofolder)
    For Each File In ofolder.files
        FileCount=FileCount+1
    Next
    For Each subfolder In ofolder.subfolders
        Foldercount=FolderCount+1
        ListFoldersandfilesc(Subfolder)
    Next  
End Sub

'function to take care of rootfolder size
function getfoldersize(ofolder)	'folder object
    if strcomp(typename(ofolder),"folder",1)<>0 then
        getfoldersize=0	'return zero by convention
        exit function
    end if
    if ofolder.isrootfolder then
        set odrv=createobject("scripting.filesystemobject").getdrive(ofolder.path)
        getfoldersize=odrv.totalsize-odrv.freespace
	set odrv=nothing
    else
        getfoldersize=ofolder.size
    end if
end function

(I can see additonal fine detail you can take more care, but the basic is working out like this.
- tsuji