Looking for a file in a folder and creating associated hyperlink
Looking for a file in a folder and creating associated hyperlink
(OP)
Hi all,
I use an excel file as BoM and I would like to look through our network drive to find associated drawing and create hyperlink to it in another column. All and every revision of the drawings are stored in the same place (ERP database) and I indeed need the link to the latest one.
I know how to get the file name I am looking for and also look through the directory but it seems my macro never find any result. I assume the mistake comes from my comparison formula "If InStr(FileName, FileSearch) > 0 Then" but I'm not sure.
Here is the code :
Any help very welcome :)
I use an excel file as BoM and I would like to look through our network drive to find associated drawing and create hyperlink to it in another column. All and every revision of the drawings are stored in the same place (ERP database) and I indeed need the link to the latest one.
I know how to get the file name I am looking for and also look through the directory but it seems my macro never find any result. I assume the mistake comes from my comparison formula "If InStr(FileName, FileSearch) > 0 Then" but I'm not sure.
Here is the code :
CODE -->
Sub LoopThroughDrawingFiles()
Dim FilePath, FileName, FileSearch As String
FilePath = "V:\ERP-doc\Doc archive"
FileName = Dir(FilePath & "\*.pdf")
FileName = CStr(FileName)
For ligne = 1 To 400
FileSearch = Left(Range("B" & ligne).Value, 14)
Do While (FileName <> "")
If InStr(FileName, FileSearch) > 0 Then
ActiveSheet.Hyperlinks.Add Anchor:=Range("F" & ligne), Address:=FilePath & FileName, TextToDisplay:=FileSearch
Exit Sub
End If
FileName = Dir
Loop
Next ligne
End Sub Any help very welcome :)
Cyril Guichard
Defense Program Manager
Belgium





RE: Looking for a file in a folder and creating associated hyperlink
CODE -->
Sub LoopThroughDrawingFiles() Dim FilePath, FileName, FileSearch As String For ligne = 4 To 400 FilePath = "V:\ERP-doc\Doc archive" FileName = Dir(FilePath & "\*.pdf") FileName = CStr(FileName) FileSearch = Left(Range("B" & ligne).Value, 15) If Len(FileSearch) > 14 Then Do While (FileName <> "") If InStr(FileName, FileSearch) > 0 Then ActiveSheet.Hyperlinks.Add Anchor:=Range("F" & ligne), Address:=FilePath & "\" & FileName, TextToDisplay:=Left(FileName, Len(FileName) - 6) FileName = Left(FileName, Len(FileName) - 4) Range("G" & ligne).Value = Right(FileName, 1) End If FileName = Dir Loop End If Next ligne End SubCyril Guichard
Space Program Manager
Belgium