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,

Sam, have a look at the free list files addin here
http://www.realezsites.com/bus/primi...e/products.php

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"SAM SEBAIHI" wrote in message
...

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,

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,

And another possible way, just change the "A2" to the first cell you want to
receive the filename and hyperlink:

Sub ListAndLinkToFiles()
'User definable values
Const FirstCell = "A2" ' first cell to get file name/link
'end of user definable values

Dim anyFileName As String ' internal use variable
Dim myFolderPath As String ' internal use variable
Dim rowOffset As Long ' pointer to next cell to put entry into

Application.FileDialog(msoFileDialogFolderPicker). Show
On Error Resume Next
myFolderPath = _
Application.FileDialog(msoFileDialogFolderPicker). SelectedItems(1) _
& Application.PathSeparator
If Err < 0 Then
Err.Clear
Exit Sub ' user cancelled
End If
On Error GoTo 0
'because Excel 2007 removed the FileSearch object,
'we'll do this the old fashioned way
'initialize anyFileName
anyFileName = Dir(myFolderPath & "*.*", vbNormal)
Do While anyFileName < "" ' Start the loop.
ActiveSheet.Range(FirstCell).Offset(rowOffset, 0).Formula = _
"=HYPERLINK(" & Chr(34) & myFolderPath & anyFileName & Chr(34) _
& "," & Chr(34) & anyFileName & Chr(34) & ")"
anyFileName = Dir ' Get next entry.
rowOffset = rowOffset + 1
Loop
End Sub


"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,

Thnk you so much Jlatham, JE and JAY. You were all great! You are all really
making a difference.

--------------------------------------------------------------------------------
Sam Sebaihi Faculty Western International University Associate Programs
(alternate email) Phone #:
(405)315-8223 Time zone: Pacific Time
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
And another possible way, just change the "A2" to the first cell you want
to
receive the filename and hyperlink:

Sub ListAndLinkToFiles()
'User definable values
Const FirstCell = "A2" ' first cell to get file name/link
'end of user definable values

Dim anyFileName As String ' internal use variable
Dim myFolderPath As String ' internal use variable
Dim rowOffset As Long ' pointer to next cell to put entry into

Application.FileDialog(msoFileDialogFolderPicker). Show
On Error Resume Next
myFolderPath = _
Application.FileDialog(msoFileDialogFolderPicker). SelectedItems(1) _
& Application.PathSeparator
If Err < 0 Then
Err.Clear
Exit Sub ' user cancelled
End If
On Error GoTo 0
'because Excel 2007 removed the FileSearch object,
'we'll do this the old fashioned way
'initialize anyFileName
anyFileName = Dir(myFolderPath & "*.*", vbNormal)
Do While anyFileName < "" ' Start the loop.
ActiveSheet.Range(FirstCell).Offset(rowOffset, 0).Formula = _
"=HYPERLINK(" & Chr(34) & myFolderPath & anyFileName & Chr(34) _
& "," & Chr(34) & anyFileName & Chr(34) & ")"
anyFileName = Dir ' Get next entry.
rowOffset = rowOffset + 1
Loop
End Sub


"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,

Thank you so much paul
--------------------------------------------------------------------------------
Sam Sebaihi Faculty Western International University Associate Programs
(alternate email) Phone #:
(405)315-8223 Time zone: Pacific Time
"Paul B" wrote in message
...
Sam, have a look at the free list files addin here
http://www.realezsites.com/bus/primi...e/products.php

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"SAM SEBAIHI" wrote in message
...

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,