Inventory all files using FileSystemObject

[lasers]

I need to inventory all files on my computer (or other computers) using MS Access and VB. The file inventory will be inserted into an Access table (I'm good with that part). I am having trouble figuring out how to navigate all the files in all the folders and subfolders on the hard drive. I've tried using several "For each subfolder in folder.Subfolders" statements, but this approach is not working.

Has anyone ever done this, or know where I might start?
Thanks

 

[Gavona]

This does what you appear to want, but in Excel.

'Option Explicit
Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File LongName:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("I3").Formula = "File Path:"
    Range("J3").Formula = "File Name:"
    Range("A3:J3").Font.Bold = True
    ListFilesInFolder "D:\", True
    ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = FileItem.Path ' & FileItem.Name
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
'        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath ' & FileItem.ShortName
        Cells(r, 9).Formula = FileItem.Path
        Cells(r, 10).Formula = FileItem.Name
        
        ' use file methods (not proper in this example)
'        FileItem.Copy "C:\FolderName\Filename.txt", True
'        FileItem.Move "C:\FolderName\Filename.txt"
'        FileItem.Delete True
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Columns("A:j").AutoFit
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Gavin

 

[Gavona]

I admit I probably did little more than tweak some code I found to create the above. This could be a useful and better informed link though!
http://www.cpearson.com/excel/RecursionAndFSO.htm

Gavin

 

[HughLerwill]

http://vbnet.mvps.org/code/fileapi/fsoapicompare.htm

may be of interest.

 

[HarleyQuinn]

I'm with Hugh on this one, a recursive search using the FindFirstFile, FindNextFile (and FindClose) API's should be significantly faster than the FileSystemObject (especially if you're going to inventory your entire hard drive(s)).

Just be sure to keep the speed up for the data manipulation/insertion (although this is still going to take a long time)

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post