Create Hyperlinks automatically

[jcmckeon]

Hello All,

I have my network path
\\str-file-01\org data\data\dept\employee training files\

I want to combine that with a folder name in this directory
Smith, John

And hopefully magic happens and the path and folder can combine and create a hyperlink

I'm not sure it can but it would save me HOURS AND HOURS

Thank you in advance

 

[ranman256]

paste this code into a module, then run : HyperEachFolderName
it will list all folder in the sheet in col A.

Public Const kDIR = "\\str-file-01\org data\data\dept\employee training files\"
Sub HyperEachFolderName()
Dim fldr
 Dim oFile
Dim StartPath  As Object
Dim FileSystem As Object
   
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set StartPath = FileSystem.GetFolder(kDIR)
   
Range("A2").Select
    For Each fldr In StartPath.SubFolders
        ActiveCell.Value = fldr.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=fldr, TextToDisplay:=fldr.Name
       
        ActiveCell.Offset(1, 0).Select  'next row
    Next
End Sub

 

[jcmckeon]

Ranman256, thank you for the reply. I'm getting a error. see attachment.
Thank you in advance.