HyperLinks to folders with the same name as excel cell value

[timofey]

Hi MrExcel community, this is my first time for posting here. Though I know the topic about matching(look up) and hyperlinks in VBA Excel has been repeated a lot. I have a macros that I would love to ask on how to code which I will explain the criteria of it down below with a picture for better visual explanation.
1. I have for example 300 casecodes from work in column A
2. I have exact same number of folders with name of these case codes.
3. I need to create hyperlinks to this folders which are on my computer.
Is it even possible? I found a way to create folders path with VBA and then with HyperLink formula create a link but this is too complex for me.
Hope you will help me ^^

 

[timofey]

Also i found this vbacode but it wont work for me. No bugs in code from troubleshooter but links are not creating only deleting old ones.

Sub SetLink()
Dim dir as String
Dim r as Range, c as Range
Dim FolderExists as Boolean

application.screenupdating = false 'turns off screen updating for speed

Set R = Range("A:A").SpecialCells(xlCellTypeConstants) 'this will select all cells in column A that have a value
ActiveSheet.Hyperlinks.Delete 'clears all hyperlinks... we're just going to add them back later...

For each c in R  'loops through all those cells one by one
 dir = "C:\Yourpathhere\" & c.Value  'change this to the path that houses your case number folders
 On Error Resume Next
   FolderExists = (GetAttr(dir) And vbDirectory) = vbDirectory
 On Error GoTo 0

 If FolderExists Then
   ActiveSheet.Hyperlinks.Add Anchor:=c.Offset(,1), Address:=dir
 End If
Next c


application.screenupdating = true 'turns screen updating back on.

End Sub

 

[John_w]

Try this macro. It expects the subfolder names to be on the active sheet in column A starting at A2. You must edit the code to change the Main_Folder string, which is the main folder containing all the subfolders.

Public Sub Create_Links_To_Folders()

    Dim cell As Range
    
    Const Main_Folder  As String = "C:\path\to\main\folder\"     'CHANGE THIS
    
    'Create hyperlinks in column A for each cell in column A starting at A2
    
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            .Hyperlinks.Add Anchor:=cell, Address:=Main_Folder & cell.Value
        Next
    End With
    
End Sub

 

[timofey]

Try this macro. It expects the subfolder names to be on the active sheet in column A starting at A2. You must edit the code to change the Main_Folder string, which is the main folder containing all the subfolders.

Public Sub Create_Links_To_Folders()

    Dim cell As Range
   
    Const Main_Folder  As String = "C:\path\to\main\folder\"     'CHANGE THIS
   
    'Create hyperlinks in column A for each cell in column A starting at A2
   
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            .Hyperlinks.Add Anchor:=cell, Address:=Main_Folder & cell.Value
        Next
    End With
   
End Sub

Awesome it works, links was created but new problem appeared

 

[John_w]

Ensure there is a back slash at the end of the Main_Folder string, as shown.

 

[timofey]

Ensure there is a back slash at the end of the Main_Folder string, as shown.

Perfect, sorry for my a little bit stupidness, and thank you very much <3 Much love