List folders?

handysmurf

Board Regular
Joined
Jan 17, 2015
Messages
63
How can I modify this code to list the folders within a directory? I tried ... unsuccessfully

Code:
Sub ListAllFile()




    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long




    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add




    'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"




    'Loop through the Files collection
    For Each objFile In objFolder.Files
    'If objFile.Name Like "*.pdf" Then
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
    'End If
    Next
    'ws.Cells(2, 1).Delete
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing




End Sub

TIA!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you elaborate on what was not successful? Also, when your code did not succeed, what was the value of sPath?
 

handysmurf

Board Regular
Joined
Jan 17, 2015
Messages
63
Oh I'm completely clueless, I just changed some of the places where it says file to folder. Not surprised I couldn't figure it out. I can recreate it if you like but I've already deleted the attempted to modify code.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What happens when you run the code you posted?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,246
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
it lists the files within a directory but not subdirectories (folders)
The code you posted lists files within a folder, not folders within a folder. Try searching this site and/or google for code that lists subfolders within a specified folder.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top