macro to match part of PDF file name in cell with folders & subfolders to open it

[Ali M]

Hi guys
I search for macro to match part of file name in cells for column C across sheets with file names in folders & subfolders
the PDF files names will be like this in column C for each sheet
REFERENCE OPERATION NUMBER TK_12346757
DEL PROCESS REF HJ123345
CHECK VERSUS GT-1234556

the PDF files names will be TK_12346757,HJ123345,GT-1234556 in folders & subfolders .
so when match the part of PDF file name in column C for each sheet with file name in folders & subfolders should be based on the last item because the last item is matched . then should hyperlink the cells in column C for each sheet and open it when click cell , if the last item is not matched when match the part of PDF file name in column C for each sheet with file name in folders & subfolders should pops message "the file is not found ".
the files will be in folders & subfolders like this
C:\Users\RAMDANI\Desktop\Folder\PRT
C:\Users\RAMDANI\Desktop\Folder\PRT\output\
C:\Users\RAMDANI\Desktop\Folder\PRT\output\operation\
thanks in advance

 

[Ali M]

this is what I got

 

[John_w]

the same error in this line

 Set filesArrayList = New mscorlib.ArrayList

I don't know why you're getting an automation error on that line. Try running this in a new workbook (with the mscorlib.dll reference set) and see if you still get the error:

Public Sub Test()
    Dim filesArrayList As mscorlib.ArrayList
    Set filesArrayList = New mscorlib.ArrayList
End Sub

If the error persists I can only suggest checking your .NET Framework installation.

 

[Ali M]

Ok I I should install .NET 3.5 to work , but just hyperlink in column C without open it file when I try open it !

 

[John_w]

but just hyperlink in column C without open it file when I try open it !

Sorry, I don't understand that.

Here's a simpler method which doesn't need any references or external libraries. It searches a string containing all the files in the folder and its subfolders, so should work as long as the string doesn't exceed about 2 billion characters.

Public Sub Find_Files_Create_Hyperlinks()

    Dim mainFolder As String
    Dim ws As Worksheet
    Dim Ccell As Range
    Dim filesList As String
    Dim p1 As Long, p2 As Long
    
    mainFolder = "C:\Users\RAMDANI\Desktop\Folder\PRT\"
    
    'Get all files in main folder and its subfolders into the filesList string
    
    Get_Files_In_Folder mainFolder, filesList
    
    'Loop all sheets

    For Each ws In ThisWorkbook.Worksheets
        
        'Loop column C cells starting at C2
        
        For Each Ccell In ws.Range("C2", ws.Cells(ws.Rows.Count, "C").End(xlUp))
        
            'Find the last part of this cell's value (the file name being sought)
            
            Ccell.Hyperlinks.Delete
            p1 = InStr(1, filesList, "\" & Mid(Ccell.Value, InStrRev(Ccell.Value, " ") + 1) & ".pdf|", vbTextCompare)
            
            If p1 > 1 Then
                p1 = InStrRev(filesList, "|", p1)
                p2 = InStr(p1 + 1, filesList, "|")
                Ccell.Worksheet.Hyperlinks.Add Anchor:=Ccell, Address:=Mid(filesList, p1 + 1, p2 - p1 - 1), TextToDisplay:=Ccell.Value
            Else
                'File not found
                If MsgBox("File not found." & vbCrLf & vbCrLf & _
                       "Sheet: " & ws.Name & ", cell: " & Ccell.Address(False, False) & vbCrLf & _
                       "File: " & Mid(Ccell.Value, InStrRev(Ccell.Value, " ") + 1) & ".pdf" & vbCrLf & vbCrLf & _
                       "Click OK to continue or Cancel to quit.", vbExclamation + vbOKCancel) = vbCancel Then Exit Sub
            End If
            
        Next
        
    Next
       
End Sub


Private Sub Get_Files_In_Folder(folderPath As String, filesList As String)

    Dim WSh As Object   'WshShell
    Dim command As String
    Dim files As Variant
    
    Set WSh = CreateObject("WScript.Shell") 'New WshShell

    'Get array of all files in specified folder and its subfolders and put them in a string delimited by "|"
    
    command = "cmd /c DIR /S /B " & Chr(34) & folderPath & Chr(34)
    files = Split(WSh.Exec(command).StdOut.ReadAll, vbCrLf)
    filesList = "|" & Join(files, "|")
    
End Sub

 

[Ali M]

Sorry, I don't understand that.

I want when click the hyperlink for any cell in column C then should open PDF file , but in reality doesn't open and show message "can't open the file"

 

[John_w]

Have you run the latest macro then? If so, are the hyperlinks it creates correct? If they aren't correct, give an example of an incorrect hyperlink and what it should be.

 

[Ali M]

Have you run the latest macro then? If so, are the hyperlinks it creates correct?

surely and create the hyperlink for all of files are existed in column C for each sheet and if the file is not found will show message inform me is not found based on your code ,but when I try clicking hyperlinked cell doesn't open file and inform me prompt "can't open file "!

 

[John_w]

but when I try clicking hyperlinked cell doesn't open file and inform me prompt "can't open file "!

But is the hyperlink correct or not? As I asked in my previous post, give an example of an incorrect hyperlink and what it should be.

 

[Ali M]

But is the hyperlink correct or not?

yes, there is no incorrect hyperlink. everything is ok . all of files are existed in folders and subfolders are matched with column C ,but I can't open when click on any cell contains hyperlink . I 'm not sure how I give you example!
the only problem is the file doesn't open despite of the cell contains hyperlink that I click .

 

[John_w]

I 'm not sure how I give you example!

Hover the mouse over the hyperlink and it displays the file address. Or right-click the cell and Edit Hyperlink to show the Address.