create folders and hyperlink to them

[abbyabby]

Hello
I have code that creates the folders but if possible would like it to also hyperlink to them at the same time. It creates them in the same folder the spreadsheet is saved in so I don't have to update the path each time I use it. Thank you in advance

This is the code I have:

Sub MakeFolders()
Dim Rng As Range
Dim maxRows, maxCols, r, c As Integer
Set Rng = Selection
maxRows = Rng.Rows.Count
maxCols = Rng.Columns.Count

For c = 1 To maxCols
r = 1

Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "\" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop

Next c

End Sub

 

[Domenic]

I have amended your code as follows...

Sub MakeFolders()

    Dim Rng As Range
    Dim maxRows As Long, maxCols As Long, r As Long, c As Long
    Dim newFolderName As String
    
    Set Rng = Selection
    
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
    
    For c = 1 To maxCols
        r = 1
        Do While r <= maxRows
            newFolderName = ActiveWorkbook.Path & "\" & Rng(r, c).Value
            If Len(Dir(newFolderName, vbDirectory)) = 0 Then
                MkDir newFolderName
                ActiveSheet.Hyperlinks.Add anchor:=Rng(r, c), Address:=newFolderName, TextToDisplay:=newFolderName
            End If
            r = r + 1
        Loop
    Next c

End Sub

By the way, you'll notice that I replaced...

Dim maxRows, maxCols, r, c As Integer

with

Dim maxRows As Long, maxCols As Long, r As Long, c As Long

That's because in your original line of code maxRows, maxCols, and r are actually being declared as Variant, not Integer. In order to declare each variable as Integer, you'll need to explicitly declare each one with the desired type, as I've done. You'll also notice that I've declared them as Long instead of Integer. That's because the total number of rows in a worksheet exceeds the value an Integer variable can hold. Also, there's no real advantage in declaring a variable as an Integer vs Long.

Hope this helps!

 

[abbyabby]

I have amended your code as follows...

Sub MakeFolders()

    Dim Rng As Range
    Dim maxRows As Long, maxCols As Long, r As Long, c As Long
    Dim newFolderName As String
   
    Set Rng = Selection
   
    maxRows = Rng.Rows.Count
    maxCols = Rng.Columns.Count
   
    For c = 1 To maxCols
        r = 1
        Do While r <= maxRows
            newFolderName = ActiveWorkbook.Path & "\" & Rng(r, c).Value
            If Len(Dir(newFolderName, vbDirectory)) = 0 Then
                MkDir newFolderName
                ActiveSheet.Hyperlinks.Add anchor:=Rng(r, c), Address:=newFolderName, TextToDisplay:=newFolderName
            End If
            r = r + 1
        Loop
    Next c

End Sub

By the way, you'll notice that I replaced...

Dim maxRows, maxCols, r, c As Integer

with

Dim maxRows As Long, maxCols As Long, r As Long, c As Long

That's because in your original line of code maxRows, maxCols, and r are actually being declared as Variant, not Integer. In order to declare each variable as Integer, you'll need to explicitly declare each one with the desired type, as I've done. You'll also notice that I've declared them as Long instead of Integer. That's because the total number of rows in a worksheet exceeds the value an Integer variable can hold. Also, there's no real advantage in declaring a variable as an Integer vs Long.

Hope this helps!

This is awesome. Thank you! Is it possible to not include the file pathway in the spreadsheet?

 

[Domenic]

This is awesome. Thank you!

You're very welcome, I'm glad I could help.

Is it possible to not include the file pathway in the spreadsheet?

Simply replace...

TextToDisplay:=newFolderName

with

TextToDisplay:=Rng(r, c).Value

 

[abbyabby]

You're very welcome, I'm glad I could help.


Simply replace...

TextToDisplay:=newFolderName

with

TextToDisplay:=Rng(r, c).Value

Thank you! My day's / weeks / months are so much easier having this little gem to use

 

[Domenic]

That's great, cheers!