Looking for a file in a folder and creating associated hyperlink

[FrenchCAD]

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 :

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

 

[FrenchCAD]

Ok guys, finally sorted it out by myself. Works like a charm

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 Sub

Cyril Guichard
Space Program Manager
Belgium