I need help creating a macro to create a hyperlink to PDF file based on cells contents. Needs to also search sub directories.

[Phantasm]

So what I have is my part numbers in row "C". What I would like is a macro that will search a network directory & its sub directories for that parts PDF & change the cell to a hyperlink to that file. The files are stored in M:\CAD\Prints\.

Also, if it is not found, can I have it look into a different location like M:\CAD\Work\?

 

[Phantasm]

Bump

 

[John_w]

Give some examples of the part number in a cell and its corresponding PDF file name. Do you mean column C?

 

[Phantasm]

Give some examples of the part number in a cell and its corresponding PDF file name. Do you mean column C?

a sample part number would be 12221001MD301. The PDF file name would be 12221001MD301.PDF. Once in a while, the PDF might have a space or 2 in it, like 1222 1001 MD301.pdf Yes, Im sorry, I did mean column C.

 

[John_w]

There are several approaches - FileSystemObject files/folders scan or a DOS dir command are two. How many files are in the 2 directories? Hundreds or thousands?

The file search would be fairly straightforward when the part number exactly matches its file name, but slightly more complicated when the file name contains spaces.

 

[offthelip]

this will do it for you, yo uwill need to changethe path and also I have set the loop at 3 just for testing:

Public serchstr As String
Public notacces As String
Public serchindex As Long


Function Recurse(spath As String, inarr As Variant) As String


    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder
    Dim myFile As File
    Dim txtp As String
    
    On Error Resume Next
    Err.Clear
    Set myFolder = FSO.GetFolder(spath)
    If Err.Number <> 0 Then
        notacces = notaccess & spath & ", "
    End If
             For Each myFile In myFolder.Files
          
            If (myFile.Name) = serchstr Then
            newf = myFile.Path
            
             MsgBox (newf)
             
             With Worksheets(1)
                .Hyperlinks.Add Anchor:=.Range(Cells(serchindex, 4), Cells(serchindex, 4)), _
                Address:=newf, _
                ScreenTip:="Whatever you want", _
                TextToDisplay:=myFile.Name
             End With
            End If


            Next


    For Each mySubFolder In myFolder.SubFolders
        Skip = False
        If Not (Skip) Then
           On Error Resume Next
            Err.Clear
             For Each myFile In mySubFolder.Files


           
            If (myFile.Name) = serchstr Then
             With Worksheets(1)
                .Hyperlinks.Add Anchor:=.Range(Cells(serchindex, 4), Cells(serchindex, 4)), _
                Address:=newf, _
                ScreenTip:="Whatever you want", _
                TextToDisplay:="myfile.name"
             End With


             
            End If
            Next
            Recurse = Recurse(mySubFolder.Path, inarr)
       End If
conti:
        Next


End Function




Sub callserch()


' change this index loop *** required
        For i = 1 To 3
         serchindex = i
         serchstr = Cells(i, 3)
         Dim spath As String
         Dim pathn As String
         Dim inarr As Variant
         Dim cnt As Long
         ' change this to the path that you require
         pathn = ActiveWorkbook.Path
         


         spath = pathn
        
         Call Recurse(spath, inarr)


       Next i
  


End Sub

Also I am outputtting the hyperlink to column D just for testing, change the range address in the hyperlink add when you are happy with it

 

[Phantasm]

Thank you very much!

 

[Phantasm]

Im getting an error at "Function Recurse(spath As String, inarr As Variant) As String" saying user defined type not defined. What is "inarr"

 

[offthelip]

It is left over from the code that used as the basis for this dev. So I think you can delete it throughout. If not it is type variant, so you could try just defining it as a public variable, Sorry about that

 

[John_w]

I think the error is referring to FileSystemObject, for which you need to add a reference to Microsoft Scripting Runtime, via Tools -> References in the VBA editor.