Macro to search for file and make hyperlink

  fewa1982 22:37 16 Feb 09
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.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

This abstract video touches on division in our technologic world

Best alternatives to iTunes for Mac | Best music players for macOS: Free your music from the…