Macro List Files and Hyperlink

[Blueridge227]

I'm looking for a solution that will allow me to list all files in a directory including ones in sub folders and then hyperlink them.

Also if its possible, I would like to show the folder name in column A and the Filename in Column B.

Any help is greatly appreciated!
thank you in advance!

 

[Weaver]

Try:

Dim fso As Variant
Dim r As Long, dst As Worksheet
Sub test()
    r = 2
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set dst = ActiveSheet
    Application.ScreenUpdating = False
    With dst.Range("A1:C1")
        .Value = Array("FOLDER", "FILE", "LINK")
        .Font.Bold = True
    End With
    writeData "C:\LOCAL\"
    dst.Range("A:C").EntireColumn.AutoFit
    Set fso = Nothing
End Sub
Sub writeData(path)
    Set fld = fso.GetFolder(path)
    With dst
        If fld.Files.Count > 0 Then
            For Each file In fld.Files
                .Cells(r, 1).Value = path
                .Cells(r, 2).Value = Mid(file, InStrRev(file, "\") + 1, 255)
                .Cells(r, 3).FormulaR1C1 = "=HYPERLINK(RC[-2]&""\""&RC[-1],""Open"")"
                r = r + 1
            Next
        End If
        If fld.subfolders.Count > 0 Then
            For Each folder In fld.subfolders
                writeData folder
            Next
        End If
    End With
End Sub

 

[Blueridge227]

Wow, that was fast

Thank you very much. Exactly what I was looking for!

 

[Weaver]

Wow, that was fast

Thank you very much. Exactly what I was looking for!

That's okay, it was relief from a very boring meeting!