Need Help with hyperlinking

[davidtown23]

I have this code for processing a list:


Sub ProcessFiles()
Dim fso As Object
Dim i As Long
Dim sFolder As String
Dim Folder As Object
Dim File As Object
Dim Files As Object
Dim cnt As Long

Set fso = CreateObject("Scripting.FileSystemObject")

sFolder = "Q:\06009 Fayette Memorial Hospital - Heart Center Remodel\Construction Administration\Shop Drawings\Mechanical Shop Drawings\Shop Drawings Received"
If sFolder <> "" Then
Set Folder = fso.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 10
For Each File In Files
If File.Type = "Adobe Acrobat Document" Then
Cells(cnt, "B").Value = File.Name
cnt = cnt + 1
End If
Next File
End If ' sFolder <> ""
If sFolder <> "" Then

End Sub

Now what I am searching for is code to create a hyperlink in column "E" of the spreadsheet for each file that lists in Column "B" from the process file code above.

Also in Column "D" can it insert the file creation date for each file?

[mdmackillop]

There's some code in here for creating hyperlinks
http://vbaexpress.com/kb/getarticle.php?kb_id=780

[Bob Phillips]

Nice code :)



Sub ProcessFiles()
Dim fso As Object
Dim i As Long
Dim sFolder As String
Dim Folder As Object
Dim File As Object
Dim Files As Object
Dim cnt As Long

Set fso = CreateObject("Scripting.FileSystemObject")

sFolder = "Q:\06009 Fayette Memorial Hospital - Heart Center Remodel\Construction Administration\Shop Drawings\Mechanical Shop Drawings\Shop Drawings Received"
If sFolder <> "" Then
Set Folder = fso.GetFolder(sFolder)

Set Files = Folder.Files
cnt = 10
For Each File In Files
If File.Type Like "Adobe Acrobat*Document" Then
Cells(cnt, "B").Value = File.Name
Cells(cnt, "D").Value = File.datelastmodified
ActiveSheet.Hyperlinks.Add Anchor:=Cells(cnt, "E"), _
Address:=File.Path, _
TextToDisplay:=File.Name
cnt = cnt + 1
End If
Next File
End If ' sFolder <> ""
If sFolder <> "" Then
End If
End Sub