Directory Listing code - Mod?

**mesh1o** · 08-13-2007, 03:08 AM

Hello, how can i specify a directory in code and copy all its filing structure, so for every xls file within the folder i specify it will list it in a spreadsheet.

Thanks for your time, RoyUK has a code on his own website for opening the xls files but i want it to list the path.

HTML Code:

-RoyUK's Code

**royUK** · 08-13-2007, 03:28 AM

Are you using Excel 2007? If you are change .xls to the relevant 2007 extension

Option Explicit


Sub Lis_All_Files()
    Dim sFil   As String
    Dim sPath  As String
    Dim r      As Long

    sPath = "C:\Documents and Settings\Roy Cox\My Documents\"    'location of files
    ChDir sPath
    sFil = Dir("*.xls")    'change or add formats
    r = 2
    Do While sFil <> ""    'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "\" & sFil) 'opens the file
        With ActiveSheet
            .Cells(r, 1).Value = sFil
            r = r + 1
        End With
        sFil = Dir
    Loop    ' End of LOOP
End Sub