Solved

Listing folders using vba

Posted on 2014-01-28
4
449 Views
Last Modified: 2014-01-29
I would like to use VBA to list all of the folders and subfolders under a specified folder on my computer.  Can anyone show me how to code this?
0
Comment
Question by:dsoderstrom
  • 2
4 Comments
 
LVL 57
ID: 39816362
You can use the Dir() function, or the file system object lib.  Below is code using Dir().

Jim.

Function FindAllMDBs(strPath As String) As Integer

  Dim strFindStr As String
  Dim lngFileSize As Long
  Dim intNumFiles As Integer
  Dim intNumDirs As Integer
 
  On Error GoTo Error_FindAllMDBs
 
  Set dbCur = CurrentDb()
  Set rstFiles = dbCur.OpenRecordset("tblMDBs", dbOpenTable)
 
  strFindStr = "*.MDB"
   
  lngFileSize = FindFiles(strPath, strFindStr, intNumFiles, intNumDirs)
 
  FindAllMDBs = True
 
Exit_FindAllMDBs:
  If Not rstFiles Is Nothing Then
    rstFiles.Close
    Set rstFiles = Nothing
  End If
 
  If Not dbCur Is Nothing Then
    Set dbCur = Nothing
  End If
   
  Exit Function

Error_FindAllMDBs:
  MsgBox "Error: " & Err.Number & " - " & Err.Description, , "Unexpected Error"
  FindAllMDBs = False
  Resume Exit_FindAllMDBs
 
End Function
Function FindFiles(path As String, SearchStr As String, FileCount As Integer, DirCount As Integer) As Long
     
      Dim FileName As String   ' Walking filename variable.
      Dim DirName As String    ' SubDirectory Name.
      Dim dirNames() As String ' Buffer for directory name entries.
      Dim nDir As Integer      ' Number of directories in this path.
      Dim i As Integer         ' For-loop counter.
      Dim db As Database
      Dim strVersion As String
      Dim strErrorMsg As String
     
      ' Set error trap.
      On Error GoTo sysFileERR
     
      ' Make sure we have a properly terminated path.
      If Right(path, 1) <> "\" Then path = path & "\"
     
      ' Search for subdirectories.
      nDir = 0
      ReDim dirNames(nDir)
      DirName = Dir(path, vbDirectory Or vbHidden)  ' Even if hidden.
      Do While Len(DirName) > 0
         ' Ignore the current and encompassing directories.
         If (DirName <> ".") And (DirName <> "..") Then
            ' Check for directory with bitwise comparison.
            If GetAttr(path & DirName) And vbDirectory Then
               dirNames(nDir) = DirName
               DirCount = DirCount + 1
               nDir = nDir + 1
               ReDim Preserve dirNames(nDir)
            End If
sysFileERRCont:
         End If
         DirName = Dir()  ' Get next subdirectory.
      Loop

      ' Search through this directory and sum file sizes.
      FileName = Dir(path & SearchStr, vbNormal Or vbHidden Or vbSystem Or vbReadOnly)
      While Len(FileName) <> 0
         FindFiles = FindFiles + FileLen(path & FileName)
         FileCount = FileCount + 1
         
         On Error Resume Next
         Err = 0
         Set db = Workspaces(0).OpenDatabase(path & FileName, False, True)
         If Err = 0 Then
          strErrorMsg = ""
          strVersion = db.Version
         Else
           Select Case Err
           
           Case 3033
             strVersion = ""
             strErrorMsg = "No permission for MDB"
           Case 3045
             strVersion = ""
             strErrorMsg = "Database in use."
           Case 3031
             strVersion = ""
             strErrorMsg = "Password protected MDB"
           Case 3343
             strVersion = "4.0"
             strErrorMsg = ""
           Case Else
             GoTo UnexpectedOpenError
           End Select
           
         End If
         
         rstFiles.AddNew
         rstFiles![Name] = FileName
         rstFiles![Location] = path
         rstFiles![DateModified] = FileDateTime(path & FileName)
         rstFiles![Version] = strVersion
         rstFiles![ErrorMsg] = strErrorMsg
         rstFiles.Update
         
         'Debug.Print path & FileName & vbTab & FileDateTime(path & FileName) & vbTab & db.version ' Include Modified Date
         db.Close
         Set db = Nothing
         ' Get next file
         FileName = Dir()
      Wend

      ' If there are sub-directories..
      If nDir > 0 Then
         ' Recursively walk into them
         For i = 0 To nDir - 1
           FindFiles = FindFiles + FindFiles(path & dirNames(i) & "\", SearchStr, FileCount, DirCount)
         Next i
      End If

AbortFunction:
      Exit Function

sysFileERR:
      If Right(DirName, 4) = ".sys" Then
        Resume sysFileERRCont ' Known issue with pagefile.sys
      Else
        MsgBox "Error: " & Err.Number & " - " & Err.Description, , "Unexpected Error"
        Resume AbortFunction
      End If

UnexpectedOpenError:
        MsgBox "Error: " & Err.Number & " - " & Err.Description, , "Unexpected Error"
        GoTo AbortFunction

End Function
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39816563
You can also issue a
dir /ad /s /b > dirlist.txt

Open in new window

command and then read the contents of the dirlist.txt file into your program.

If you want to explore that solution, let me know.
0
 

Author Closing Comment

by:dsoderstrom
ID: 39817896
You suggestion worked perfectly for what I was doing.  My goal was to find duplicate files within a folder tree.  Once I ran the command you suggested and imported the data into an Access table I was able to query the information I needed.  Thank You
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39817973
If you are going after the files, you can change or remove the /ad parameter on the dir command.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question