VBA Drive Directory

[christian2016]

Hi Guys,

Need a code which list the full file path of all folders, sub folders and files held on a shared drive and writes the results within excel. So basically everything stored on the drive.

example:
Column A
C:\new folder\test.doc
C:\new folder\test2.xls
etc.

There are a lot of files on the drive so looking at something that will do it fairly quick. Less than 5 minutes.

I know I can do this in CMD but i need a vba to do it and unsure where to start.

Any help is greatly appreciated.

thanks

 

[John_w]

You can run a DOS DIR command from VBA and capture the output. Change the folder path to suit:

Public Sub DOS_Dir3()

    Dim folder As String
    Dim dirLines As Variant
    Dim results() As String
    Dim i As Long
        
    folder = "Z:\"
    
    dirLines = Split(CreateObject("wscript.shell").exec("cmd /c DIR /B /S /A-D """ & folder).StdOut.ReadAll, vbCrLf)    
    
    ReDim results(UBound(dirLines), 0)
    For i = 0 To UBound(dirLines)
        results(i, 0) = dirLines(i)
    Next
    
    With ActiveSheet
        .Cells.Clear
        .Range("A1").Resize(UBound(dirLines) + 1, 1).Value = results
    End With
    
End Sub

 

[christian2016]

Thanks John, works well and fast.

just a question when I run from CMD T:\>dir /b /s >testlist.txt i get a lot more results.

Is there something we can change in the code or is there a reason as to why this is happening. Also does it get filenames longer than 250 characters.

Thanks for your help

 

[John_w]

The /A-D omits directories so that could be the reason. I don't know about the 250 character file names.