Macro to insert a hyperlink to external file

[dadouza]

Hi,

I'm using the following code to insert a file name of an external file into
a cell in an Excel spreadsheet, and it works fine. What I would really like
to do is make that file name a hyperlink to the file so I can click and open
it. I've searched many sites and tried many things but I just can't get it to
work for me. Is there anybody out there who can help me please?

Thanks for looking at my question.

Regards
Paul


=============================================================================
'- SEARCH ALL FILES IN A FOLDER & FIND STRING IN FILE NAME
'- PUT NAMES INTO ACTIVE SHEET AT BOTTOM OF COLUMN A
'- (search is not case sensitive)
'- Brian Baulsom July 2008
'=============================================================================
Sub FIND_DRAWING()
Dim FindText As String
Dim MyFolder As String
Dim MyFileCount As Integer
Dim MyFileName As String
Dim MyFileType As String
Dim f
Dim WS As Worksheet
'-------------------------------------------------------------------------
'- SET VARIABLES
Set WS = ActiveSheet
MyFolder = "H:\SERVICE CENTRE DETAILS\INSPECTION DRAWINGS and
DOCUMENTS\Misc Drawings"
FindText = WS.Range("B2").Value
MyFileType = "*" & FindText & "*.*" ' = "*Test*.*"
'-------------------------------------------------------------------------
'- CHECK FILE NAMES
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.Filename = MyFileType
.SearchSubFolders = False ' True to search subfolders
'---------------------------------------------------------------------
'- RESULTS
MyFileCount = 0
If .Execute() > 0 Then
MyFileCount = .FoundFiles.Count
For f = 1 To MyFileCount
MyFileName = .FoundFiles(f)
WS.Range("H2").Value = WS.Range("H2").Value & " " & MyFileName

Next
Else
MsgBox ("Search for file names containing : " & FindText & vbCr _
& "No matches found")
Exit Sub
End If
End With

'--------------------------------------------------------------------------
'- finish
MsgBox ("Found " & MyFileCount & " file names.")
End Sub

 

[BizMark]

I believe the line you are looking for to insert after:

WS.Range("H2").Value = WS.Range("H2").Value & " " & MyFileName

is:

WS.Hyperlinks.Add anchor:=WS.Range("H2"), target:= MyFileName

Regards,
Mark

 

[dadouza]

Hi,
I tried that but the macro stops at your line and highlights the word 'target'
Thanks for your help. Also some drawings have more than 1 sheet, can the
code be tweaked to get all the drawings and put them in H2, H3, H4 and so on?

Regards

paul