How to automatically hyperlink to folders from Excel

I have 500 folders with a specific string of names e.g., Aseem-tb-rd-001, Taj-tb-rd-006, etc..

Each folder has two sub-folders, exactly named In and Out.

Now I have an Excel file with sheets named In and Out in it, Both these sheets have the respective string of names. I need Excel to automatically fetch the folder and add the hyperlink.

e.g., if cell H3 in sheet In has the name Taj-tb-rd-006, I want the hyperlink to link to the folder Taj-tb-rd-006\In and if cell H8 in sheet Out has the name Taj-tb-rd-006, I want the hyperlink to link to the folder Taj-tb-rd-006\Out.

Can this be done by any means?

• Welcome to SU! Yes, you can write a VBA macro that uses the content of the cells to make a hyperlink. – Leo Chapiro Jan 3 '17 at 14:03
• There is a formula called hyperlink. A combo of this formula and the name manager is how I would approach this task. IS this enough or is there more that you need? – bvaughn Jan 3 '17 at 14:46
• If you could help me with how to do it, I would be more than gratefull. – muhammed ayyoob Jan 3 '17 at 15:12

Edit Sorry, misread the question. The other half of the answer is that you need some VBA code like this, modified from this answer on SO:

Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant, i As Integer
file = Dir("D:\My Folder\", vbDirectory)
While (file <> "")
i = i + 1
ActiveSheet.Cells(i, 1) = file
file = Dir
Wend
End Sub

• Could you elaborate it please. I'm a complete idiot in this . – muhammed ayyoob Jan 3 '17 at 15:14
• I've added code that will pull the directory names from the given folder into a column in your active sheet. Run it for one of your worksheets, then copy it into the other. Use the formula to get the hyperlinks, one ending in \in and one ending in \out. – TheCatsTail Jan 3 '17 at 15:18