Create a list of Excel Files in a Worksheet

Here's a code to create a list of Excel files in your worksheet.

This will give me a list of all the Excel files in the folder

C:\Users\Dorothy\Documents\Consulting.

Sub ListExcelFiles()
Const ArrTop As Integer = 300
Dim MyDir As String
Dim FN As String
Dim MyArray(ArrTop) As String
Dim i, j As Integer

On Error Resume Next

MyDir = "\\tsclient\C\Users\Dorothy\Documents\Consulting\"
FN = Dir(MyDir & "\*.xls*", vbDirectory)

i = 1
Do While (Len(FN) > 0) And (i < 101)
  
    If Not (FN = ".") Or (FN = "..") Then
'        MyArray(i) = FN
        Range("a" & i) = FN
        i = i + 1
    End If

    ' Get the next file.
    FN = Dir()
Loop

End Sub