Create file of directories 3

[ohmbru]

I'm not sure if VBA is the way to go here, but I want to be able to create a file (csv, Excel, txt) that lists all files in a directory/drive on my network and include the file size and date.



Brian

 

[Tranman]

Brian,
Yes, that can be done with VBA.

You can do that with the FileSystemObject. You will need a reference to the "Microsoft Scripting Runtime" in your project.

Just add the reference, then use the "Object Browser" (press F2 when you're in the code window), in order to explore the methods/properties/events of the object.

If you don't know how to do any of this stuff, write back and I can bang out a snippet for you.

If you do write back, be sure to say which date you are interested in--Created/Modified/Accessed.

Tranman

 

[Tranman]

Brian,
I had to kick off an update that will take about 1.5 hours, so while it's running, I wrote that snippet for you:

Private Sub ListFiles()
Dim strName As String
Dim strSize As String
Dim strCDate As String
Dim strCSV As String
Dim fso As New FileSystemObject
Dim fol As Folder
Dim fil As File

Set fol = fso.GetFolder("C:\AAATest")
'BTW, you really should use UNC (Universal Naming
'Convention) here, as in:
'Set fol = fso.GetFolder("\\myServer\myShare")
'So every user does not have to have the share mapped as the
'same drive letter.

For Each fil In fol.Files
strName = fil.Name
strSize = fil.Size
strCDate = fil.DateCreated
strCSV = strName & "," & strSize & "," & strCDate
'Then just write strCSV out to a flat file...
'Puts the list in the same place as the folder you're
'examining. You could put it anywhere you wish.
Open fol.Path & "\FileList.csv" For Append As #1
Print #1, strCSV
Close #1
Next

End Sub

 

[123FakeSt]

Your better off with VB script here (won't need to rely on an application) like this. It will make C:\ListFiles.txt for everything in your Program Folders Folder.

It searches recursively through the subfolders.

Set objFSO = CreateObject("Scripting.FilesystemObject")
Set objWSH = WScript.CreateObject("WScript.Shell")
strPath = "C:\Program Files"
Set objFolder = objFSO.GetFolder(strPath)
getSubs(objFolder)
Set objOCRFolder = nothing
Set objFSO = nothing
Wscript.quit

Sub getSubs (mainFolder)
Set objFolder = objFSO.Getfolder(mainFolder)
For each objFile in objFolder.files
Set fileDest = objFSO.OpenTextFile("C:\ListFiles.txt", 8, True)
fileDest.writeline (objFolder & "\" & objFile.Name & "," & objFile.Size)
Set fileDest = nothing
Next

For each objSubFolder in mainFolder.Subfolders
getSubs (objSubfolder)
Next
End Sub

The early bird gets the worm, but the second mouse gets the cheese.

 

[ohmbru]

Tranman,
Thanks, that code work great!

It does not, however, get the files in subfolders like Fake's VB example. Is there a way to do that with VBA?

Brian

 

[ohmbru]

Fake,

I am not as familiar with VB, although I do have VB 6.0 and get get around a little. I tried your code, but get an error (object required). I think I am missing a reference. Can you tell me what I need in terms of references to execute?



Brian

 

[123FakeSt]

You can just paste the code i wrote into a notepad file or any text file, than change the extension to .vbs

You will need to be on Windows 2000, XP etc. for it to work in visual basic script.

If not we can add the object references and stick it in an access module ... like this. I would change the SearchSubfolders to accept both the Folder and where to create the text file to be more practical.

Sub GetFileListing()
    Dim strFolder As String
    strFolder = "C:\Program Files"
    SearchSubfolders (strFolder)
End Sub


Sub SearchSubfolders(strMainFolder As String)

    Dim objMainFolder           'Holds main folder
    Dim objFSO As Object        'So we can use the File System Object
    Dim objFile As Object       'Holds the file objects
    Dim objSubfolders As Object 'Holds the subfolder objects
    Dim strFileName As String   'Holds the string name of the file
    Dim objFileDest As Object   'File we are creating the log on
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set objMainFolder = objFSO.GetFolder(strMainFolder)

    For Each objFile In objMainFolder.Files
            strFileName = objFile.Name
            Set objFileDest = objFSO.OpenTextFile("C:\Temp\AllFiles.txt", 8, True)
            objFileDest.WriteLine objMainFolder.Name & "\" & objFile.Name & "," & objFile.Size
            Set objFileDest = Nothing
    Next

    For Each objSubfolders In objMainFolder.subfolders
            SearchSubfolders (objSubfolders)
    Next

    Set objMainFolder = Nothing
    Set objFSO = Nothing
    Set objFile = Nothing
    Set objSubfolders = Nothing

End Sub

The early bird gets the worm, but the second mouse gets the cheese.

 

[VBslammer]

I would use early binding to make the coding easier, and also include the full path to each file so the folder structure is more obvious in the output file.

The file size can also be converted to KB so it resembles the explorer file size.

Sub SearchSubfolders(strMainFolder As String)
  Dim fso           As Scripting.FileSystemObject
  Dim oFile         As Scripting.File
  Dim ioFileOut     As Scripting.TextStream
  Dim oParentFolder As Scripting.Folder
  Dim oChildFolder  As Scripting.Folder
  
  Set fso = New Scripting.FileSystemObject
  Set oParentFolder = fso.GetFolder(strMainFolder)

  For Each oFile In oParentFolder.Files
    Set ioFileOut = fso.OpenTextFile("C:\AllFiles.txt", ForAppending, True)
    ioFileOut.WriteLine [blue]oParentFolder.Path[/blue] & "\" & oFile.Name & _
                        [blue]" (" & Round((oFile.Size / 1024) + 0.5) & " KB)"[/blue]
    Set ioFileOut = Nothing
  Next

  For Each oChildFolder In oParentFolder.SubFolders
    SearchSubfolders (oChildFolder)
  Next
  
  Set oChildFolder = Nothing
  Set oParentFolder = Nothing
  Set oFile = Nothing
  Set fso = Nothing
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ohmbru]

Thanks all!

good stuff!


Brian