Hyperlink folder directory

[ysherriff]

To all how are you? I have the following macro that loops through a directory and lists the folder in each directory. What I am trying to do also is each folder that is listed, also creates a hyperlink in the excel file to the folder. Everything is working except the hyperlink. Can someone help me please. I have attached the file as well.

Sub List_Folders_In_Directory()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("S:\Monthly Incentive Calculator")
i = 1
'loops through each file in the directory and prints their names and path
For Each objSubFolder In objFolder.subfolders
  'print folder name
  Cells(i + 1, 1) = objSubFolder.Name
 
  i = i + 1
  'create hyperlink in selected cell
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
  objSubFolder.Path, _
  TextToDisplay:=objSubFolder.Name
 
  'print folder path
  ' Cells(i + 1, 2) = objSubFolder.Path
  
Next objSubFolder
End Sub

 

[Hui]

Try the Following code:

Sub List_Folders_In_Directory()
Dim objFSO As Object
Dim objFolder As Object
Dim objSubFolder As Object
Dim i As Integer
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder("C:\Users\Dads\Downloads") 'S:\Monthly Incentive Calculator
i = 1
'loops through each file in the directory and prints their names and path
For Each objSubFolder In objFolder.subfolders
'create hyperlink in selected cell
Cells(i + 1, 1).Hyperlinks.Add _
  Anchor:=Cells(i + 1, 1), _
  Address:=objSubFolder.Path, _
  TextToDisplay:=objSubFolder.Name
  
  'print folder path
  'Cells(i + 1, 2) = objSubFolder.Path
  i = i + 1
Next objSubFolder
End Sub

 

[ysherriff]

Thank you Hui. I see where i might have missed the information. I missed the counter/loop in the hyperlink.

Thanks

 

[Hui]

You also need
i = i + 1
To be the last line in the loop