One way:

Public Sub CreateHyperlinkDirectory()
Const sPATH = "<your path here"
Dim sFileName As String
Dim rDest As Range
sFileName = Dir(sPATH)
With Worksheets.Add(Befo=Sheets(1))
On Error Resume Next
.Name = "Directory"
On Error GoTo 0
Set rDest = .Range("A1")
Do While sFileName < vbNullString
.Hyperlinks.Add _
Anchor:=rDest, _
Address:=sPATH & sFileName, _
TextToDisplay:=sFileName
Set rDest = rDest.Offset(1, 0)
sFileName = Dir
Loop
End With
End Sub


In article ,
"SAM SEBAIHI" wrote:

I would like to have a macro that if I execute it, it will copy all the
existing file names from a specified directory into individual cells in the
sheet. each individual file name will be hyperlinked, if I click on it, the
file should open (they could be Word or pdf files..etc..)

I really appreciate your help in advance

Thank you,