On Jun 3, 2:01*pm, mrsviqt wrote:
I have the code below that gives me the file names within a certain path. *I
now want a macro that will give me the subfolders and files within a drive
(for example, instead of Q:\PDF\mis90 it will say M:\Amyand will list the
folder names, and the files within those folders). *Is this even possible?
Thanks in advance for any assistance you may provide.

Option Explicit

Public Sub Tester()

* * Dim WB As Workbook
* * Dim SH As Worksheet
* * Dim destRng As Range
* * Dim oFSO As Object
* * Dim oFolder As Object
* * Dim ofile As Object
* * Dim sFolderName As String
* * Dim i As Long

* * Const sPath As String = _
* * * * * *"Q:\PDF\mis_90"

* * Set WB = Workbooks("MyBook.xls")
* * Set SH = WB.Sheets("Sheet1")
* * Set destRng = SH.Range("B1")

* * Set oFSO = CreateObject("Scripting.FileSystemObject")
* * sFolderName = sPath & Application.PathSeparator

* * On Error Resume Next
* * Set oFolder = oFSO.GetFolder(sFolderName)
* * On Error GoTo XIT
* * If Not oFolder Is Nothing Then
* * * * For Each ofile In oFolder.Files
* * * * * * destRng.Offset(i).Value = ofile.Name
* * * * * * i = i + 1
* * * * Next ofile
* * End If

XIT:
* * Set ofile = Nothing
* * Set oFolder = Nothing
* * Set oFSO = Nothing
End Sub


This is an example of what you said you want. If you want the
subfolders of the subfolders, then you need something else.

Dim fso
Set fso = CreateObject("scripting.filesystemobject")

Dim fil
Dim fol
Dim SubFol

Set fol = fso.GetFolder("C:\")

For Each SubFol In fol.SubFolders
WScript.Echo " "
WScript.Echo SubFol.Path
WScript.Echo "--------------------"
For Each fil In SubFol.Files
WScript.Echo fil.Path
Next
Next