Excel

Automatic Directory File List

Ease of Use

Easy

Version tested with

2000 

Submitted by:

XLGibbs

Description:

Occasionally it becomes useful to be able to create a list of files in a particular directory, including their subfolders. This code will prompt the user for a path to perform the function, then create a new workbook with the file names, size, created, modified, last accessed dates, and the full path name. 

Discussion:

Many times, we create so many files for our job, that we may lose track of them. Instead of having to do a windows search of a directory, you can create a list of the files for reference. This code is one step solution to that need. Only file names are listed, and it is not a directory "tree" as it will not show the subfolder names with the exception of their appearance in the full path name. The code can be executed from any excel module. The only caveat is needing to set a reference to the Windows Script Host Object Model in the Visual Basic Editor references 

Code:

instructions for use

			

'Code goes in a standard module '''''MUST SET REFERENCE to WINDOWS SCRIPT HOST OBJECT MODEL'''''''''''' Option Explicit Sub PopulateDirectoryList() 'dimension variables Dim objFSO As FileSystemObject, objFolder As Folder Dim objFile As File, strSourceFolder As String, x As Long, i As Long Dim wbNew As Workbook, wsNew As Worksheet ToggleStuff False 'turn of screenupdating Set objFSO = New FileSystemObject 'set a new object in memory strSourceFolder = BrowseForFolder 'call up the browse for folder routine If strSourceFolder = "" Then Exit Sub Workbooks.Add 'create a new workbook Set wbNew = ActiveWorkbook Set wsNew = wbNew.Sheets(1) 'set the worksheet wsNew.Activate 'format a header With wsNew.Range("A1:F1") .Value = Array("File", "Size", "Modified Date", "Last Accessed", "Created Date", "Full Path", "Size") .Interior.ColorIndex = 7 .Font.Bold = True .Font.Size = 12 End With With Application.FileSearch .LookIn = strSourceFolder 'look in the folder browsed to .FileType = msoFileTypeAllFiles 'get all files .SearchSubFolders = True 'search sub directories .Execute 'run the search For x = 1 To .FoundFiles.Count 'for each file found, by the count (or index) i = x 'make the variable i = x If x > 60000 Then 'if there happens to be more than multipls of 60,000 files, then add a new sheet i = x - 60000 'set i to the right number for row placement below Set wsNew = wbNew.Sheets.Add(after:=Sheets(wsNew.Index)) With wsNew.Range("A1:F1") .Value = Array("File", "Parent Folder", "Full Path", "Modified Date", _ "Last Accessed", "Size") .Interior.ColorIndex = 7 .Font.Bold = True .Font.Size = 12 End With End If On Error GoTo Skip 'in the event of a permissions error Set objFile = objFSO.GetFile(.FoundFiles(x)) 'set the object to get it's properties With wsNew.Cells(1, 1) 'populate the next row with the variable data .Offset(i, 0) = objFile.Name .Offset(i, 1) = Format(objFile.Size, "0,000") & " KB" .Offset(i, 2) = objFile.DateLastModified .Offset(i, 3) = objFile.DateLastAccessed .Offset(i, 4) = objFile.DateCreated .Offset(i, 5) = objFile.Path End With ' Next objFile Skip: 'this is in case a Permission denied error comes up or an unforeseen error 'Do nothing, just go to next file Next x wsNew.Columns("A:F").AutoFit End With 'clear the variables Set objFolder = Nothing Set objFile = Nothing Set objFSO = Nothing Set wsNew = Nothing Set wbNew = Nothing ToggleStuff True 'turn events back on End Sub Sub ToggleStuff(ByVal x As Boolean) Application.ScreenUpdating = x Application.EnableEvents = x End Sub Function BrowseForFolder(Optional OpenAt As Variant) As Variant '''Code from kpuls, www.VBAExpress.com..portion of Knowledge base submission ''www.codeguru.com Dim ShellApp As Object Set ShellApp = CreateObject("Shell.Application"). _ BrowseForFolder(0, "Please choose a folder", 0, OpenAt) On Error Resume Next BrowseForFolder = ShellApp.self.Path On Error GoTo 0 Set ShellApp = Nothing Select Case Mid(BrowseForFolder, 2, 1) Case Is = ":" If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid Case Is = "\" If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid Case Else GoTo Invalid End Select Exit Function Invalid: ToggleStuff True End Function

How to use:

  1. 1.Open the Visual Basic Editor by hitting Alt-F11 or Tools>Macro>Visual Basic Editor from the toolbar.
  2. 2.Copy the code and paste it in full into a standard module (you can right click the project names on the left to insert>module)
  3. 3.Go to Tools>References and place a check next to "Windows Script Host Object Model"
  4. 4. Run the code.
  5. 5. OR, Download the attached file and press the button.
 

Test the code:

  1. 1. Run the code.
  2. 2. OR, Download the attached file and press the button.
  3. 3. A new file will appear with the list of files and other attributes from the folder you select from the browser window that appears.
 

Sample File:

DirectoryListGenerator.zip 15.7KB 

Approved by mdmackillop


This entry has been viewed 739 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express