List File Hyperlink

[peterwmartin]

Hi can someone please help me getting this bit of code to give the filename as a hyperlink to the file?
Thanks

Sub ImportFileList()
Dim MyFolder As String 'Store the folder selected by the using
Dim FiletoList As String 'store the name of the file ready for listing
Dim NextRow As Long 'Store the row to write the filename to
On Error Resume Next
Application.ScreenUpdating = False
'Display the folder picker dialog box for user selection of directory
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\"
End With
'Dir finds the first Excel workbook in the folder
FiletoList = Dir(MyFolder & "*.pdf")
Range("A1").Value = "Filename"
Range("B1").Value = "Date Last Modified"
Range("C1").Value = "Hyperlinks"
Range("A1:B3").Font.Bold = True
'Find the next empty row in the list
NextRow = Application.CountA(Range("A:A")) + 1
'Do whilst the dir function returns an Excel workbook
Do While FiletoList <> ""
Cells(NextRow, 1).Value = FiletoList 'Write the filename into the next available cell
Cells(NextRow, 2).Value = FileDateTime(MyFolder & FiletoList) 'Write the date the cell was last modified

Cells(NextRow, 3).Hyperlink.Add = FiletoList 'Write the date the cell was last modified
NextRow = NextRow + 1 'Move to next row
FiletoList = Dir 'Dir returns the next Excel workbook in the folder
Loop
Application.ScreenUpdating = True
End Sub

[p45cal]

change:
Cells(NextRow, 3).Hyperlink.Add = FiletoList
to:
Cells(NextRow, 3).Hyperlinks.Add Cells(NextRow, 3), Address:=MyFolder & FiletoList
or to:
Cells(NextRow, 3).Hyperlinks.Add Cells(NextRow, 3), Address:=MyFolder & FiletoList, TextToDisplay:=FiletoList
depending on what you want to SEE in the cell.

[peterwmartin]

thanks for the help p45cal