Macro to search for file and make hyperlink

  fewa1982 22:37 16 Feb 2009
Locked

Hello all,

I have a list of document numbers and pdfs of those document numbers.

What I want to do is search in a folder for one of those document numbers and if found create a hyperlink to it and repeat.

I have code that does it for one cell at a time, and I haven't been able to tweek it to do multiple cells.

---------------------------------------------
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 = "F:\CUMMINS\SCANS\Invoices\"
FindText = WS.Range("A2").Value
MyFileType = "*" & FindText & "*.*" ' = "*Test*.*"
'-------------------------------------------------------------------------
'- CHECK FILE NAMES
With Application.FileSearch
.NewSearch
.LookIn = MyFolder
.Filename = MyFileType
.SearchSubFolders = True ' True to search subfolders
'---------------------------------------------------------------------
'- RESULTS
MyFileCount = 0
If .Execute() > 0 Then
MyFileCount = .FoundFiles.Count
For f = 1 To MyFileCount
FoundRow = FoundRow + 1
MyFileName = .FoundFiles(f)
WS.Range("C" & CStr(FoundRow)).Value = WS.Range("C" & CStr(FoundRow)).Value & " " & MyFileName
WS.Hyperlinks.Add Anchor:=WS.Range("C" & CStr(FoundRow)), Address:=MyFileName, TextToDisplay:=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

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

WWDC history: Apple's product launches since 2005

Comment importer des contacts d’un iPhone à un autre iPhone ?