CODEWITHSUNDEEP
excelvbahyperlinkpath
gluc7
gluc7

Reputation: 575

Using # in a path name for a hyperlink in excel vba

I am creating a vba For loop to cycle through cells in a range and creating a hyperlink to file folders based on the text within the cell. See below:

Set rng = Worksheets("Sheet1").Range("A1:A100")

For Each cell In rng

    address1 = "C:\Users\Desktop\Tests\Comm Review\Item #" & 
    cell.Text

    If Not IsEmpty(cell) Then
        Worksheets("Sheet1").Hyperlinks.Add Anchor:=cell, Address:=address1, TextToDisplay:=cell.Text
    End If
Next cell

The cell value will be something like 1001.T0502 and the actual folder name that I am linking to will be Item #1001.T0502. So in my address1 variable i create the path to the folder.

However, when I do this it creates the path with everything but #1001.T0502 and ends up stopping at "\Item". If I were to drop the number sign(#) though it includes the number and ends up being Item 1001.T0502. For some reason the number sign stops it from making the correct path. What am I missing here? There are already 200 folders with the number sign in the folder name so going back now and taking it out would be too much work.

Any help would be appreciated. Thanks!

Upvotes: 2

Views: 2329

Answers (1)

K Paul
K Paul

Reputation: 144

You cannot use a pound character in a file name for a hyperlink in an Office program. See official Microsoft documentation here:

https://support.microsoft.com/en-us/help/202261/you-cannot-use-a-pound-character-in-a-file-name-for-a-hyperlink-in-an

Seems totally wacko if you ask me, but alas, I think you're trying to solve an unsolvable problem.

But, fear not, I did think of a potential work around. Instead of making the cells actual hyperlinks, you could just recolor the cell to blue with an underline and then use this little trick to capture when the cell is selected and open Windows Explorer to the corresponding file path.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
            Shell "explorer ""C:\Users\Desktop\Tests\Comm Review\Item #" & Target.Value & """", vbNormalFocus
        End If
    End If
End Sub

The only downside I can see here is that selecting the cell with the arrow keys also opens the corresponding folder. There may be a work around to that, but I don't have time at the moment to research it.

I hope this helps!

Upvotes: 1

Related Questions