Need Help-Add Image to a hyperlink

Hi,

I have a macro created by Leith Ross that extracts the name of the file and creates its hyperlink in different columns according to its extension like jpg, pdf etc. I need io replace the hyperlink with a image i.e instead of showing the full path of hyperlink , it should display any image and if I click on that image it should open the respective file. You can see previous thread through the link :
http://www.excelforum.com/showthread.php?t=647491

here is the code by Leith Ross:

Sub ListFilesAndHyperlinks()

Dim C As Variant
Dim FileDir As Variant
Dim FileExt As String
Dim FileName As String
Dim FSO As Object
Dim NamePos As Variant
Dim PrevFile As String
Dim R As Long

FileDir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & vbCrLf & vbCrLf & _
"for example:C:\Microarray")

If FileDir = "" Then Exit Sub

If Right(FileDir, 1) <> "\" Then
FileDir = FileDir & "\"
End If

R = 1 'Start listing at row 2. Headers in row 1
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each F In FSO.GetFolder(FileDir).Files
FileName = Split(F.Name, ".")(0)
FileExt = FSO.GetExtensionName(F)
FP = F.Path
Select Case FileExt
Case Is = "jpg"
C = "C": GoSub AddLink
Case Is = "pdf"
C = "D": GoSub AddLink
End Select
Next F

Set FSO = Nothing
Exit Sub

AddLink:
If FileName <> PrevFile Then R = R + 1
Cells(R, C).Hyperlinks.Add Anchor:=Cells(R, C), Address:=F.Path
NamePos = Split(FileName, "_")
Cells(R, "A") = NamePos(0)
On Error Resume Next
Cells(R, "B") = NamePos(1)
Err.Clear
PrevFile = FileName
Return

End Sub


I need to modify the code to add image to a hyperlink .

Thanks

Ashish

Ashish,

I'm sure when Leith posted his code, he used Code Tags. Please edit your post to do so.

Hi,

There was no edit option, so I am posting the thread again.
I have a macro created by Leith Ross that extracts the name of the file and creates its hyperlink in different columns according to its extension like jpg, pdf etc. I need io replace the hyperlink with a image i.e instead of showing the full path of hyperlink , it should display any image and if I click on that image it should open the respective file. You can see previous thread through the link :
http://www.excelforum.com/showthread.php?t=647491

here is the code by Leith Ross:

Sub ListFilesAndHyperlinks()

Dim C As Variant
Dim FileDir As Variant
Dim FileExt As String
Dim FileName As String
Dim FSO As Object
Dim NamePos As Variant
Dim PrevFile As String
Dim R As Long

FileDir = InputBox("Input the path containing the files you " & _
"want to list on your worksheet" & vbCrLf & vbCrLf & _
"for example:C:\Microarray")

If FileDir = "" Then Exit Sub

If Right(FileDir, 1) <> "\" Then
FileDir = FileDir & "\"
End If

R = 1 'Start listing at row 2. Headers in row 1
Set FSO = CreateObject("Scripting.FileSystemObject")

For Each F In FSO.GetFolder(FileDir).Files
FileName = Split(F.Name, ".")(0)
FileExt = FSO.GetExtensionName(F)
FP = F.Path
Select Case FileExt
Case Is = "jpg"
C = "C": GoSub AddLink
Case Is = "pdf"
C = "D": GoSub AddLink
End Select
Next F

Set FSO = Nothing
Exit Sub

AddLink:
If FileName <> PrevFile Then R = R + 1
Cells(R, C).Hyperlinks.Add Anchor:=Cells(R, C), Address:=F.Path
NamePos = Split(FileName, "_")
Cells(R, "A") = NamePos(0)
On Error Resume Next
Cells(R, "B") = NamePos(1)
Err.Clear
PrevFile = FileName
Return

End Sub

I need to modify the code to add image to a hyperlink .

Thanks

Ashish

Thank you, Ashish82.