Excel – How to automatically hyperlink to folders from Excel

microsoft excelmicrosoft-excel-2007microsoft-excel-2010

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?

Best Answer

You can do it using a formula. I would start with:

=HYPERLINK(H3&"\out")

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
Related Question