Create hyperlink to folders based on values in col A

**Biinge** · 12-23-2011, 05:24 AM

Hi

I have a variable range from A4 to A10000 (About)

I want to make a macro that loops through the values (text) in these cells and searches for matching folder names in three different places.
If there is match then make the cell hyperlinked to that folder.

There is one more case : The folder text contains more text than the cell text, so the macro should only match the 9 first letters (Wich is the total number of letters in each cell)

Could anyone point me in the right direction with this one?

**Biinge** · 12-29-2011, 08:45 AM

Anyone got any pointers, or do you need more information?

**Firefly2012** · 12-29-2011, 08:58 AM

Hi

This will get you started:

Sub make_hyperlinks()
Const FLD = "D:\Work\Test\"   'don't forget the final \!!!

Dim rngFolders As Range, rngEachFolder As Range

Set rngFolders = Range("A4:A" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each rngEachFolder In rngFolders
    If Len(Dir(FLD & rngEachFolder & "*", vbDirectory)) > 0 Then
        ActiveSheet.Hyperlinks.Add anchor:=rngEachFolder, Address:=FLD & Dir(FLD & rngEachFolder & "*", vbDirectory)
    End If
Next rngEachFolder
    
End Sub

The Constant FLD is where you initially start your search for the folders. Potentially you could loop thru several foilders if you want. Note that I find Dir using vbDirectory argument to be rather slow.