Solved

Listing folders using vba

Posted on 2014-01-28
4
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 58
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

726 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