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

Question

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!

I ran your code on a single cell, which contained 1407.T0502. The hyperlink created was: C:\Users\Desktop\Tests\Comm Review\Item#1407.T0502 Note that the space was removed, but the number was still there. — Ron Rosenfeld
– Ron Rosenfeld, Commented Aug 10, 2017 at 2:45
Yeah, I think I tried testing that before too and it worked, but all the folders are unfortunately named "Item #..." with a space between item and #. I'm assuming vba has a problem with the number sign by itself, but it shouldn't as its within a string. Thanks for your help though! — gluc7
– gluc7, Commented Aug 10, 2017 at 3:07
To be able to include the space, try surrounding the link address with double quotes. But I cannot check it this evening. (something like address1 = """C:\Users\Desktop\Tests\Comm Review\Item #" & cell.Text & """" — Ron Rosenfeld
– Ron Rosenfeld, Commented Aug 10, 2017 at 3:08
No worries. Just tried that, and still no luck unfortunately. I even tried `"C:\Users\Desktop\Tests\Comm Review\Item" & " " & "#" & cell.Text' and once again it just stops before the number sign. — gluc7
– gluc7, Commented Aug 10, 2017 at 3:28
On a side note, renaming folders wouldn't really be much work. Just write a quick script to go remove those hash symbols. See technet.microsoft.com/en-us/library/ee198701.aspx and technet.microsoft.com/en-us/library/ee198721.aspx. — K Paul
– K Paul, Commented Aug 10, 2017 at 3:45

K Paul · Accepted Answer · 2017-08-10 03:37:41Z

1

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!

answered Aug 10, 2017 at 3:37

K Paul

14410 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

gluc7 Over a year ago

I wish I knew that before we created all the folders haha. Oh well. It appears yours looks like it would suffice as a work around. I'll have to test it out more tomorrow morning. I just worry about the long term reliability of that solution with multiple people using the spreadsheet compared to having an actual "clickable" hyperlink. Thanks for your help everybody!

K Paul Over a year ago

Honestly, if I were you, I'd write a quick script to remove the hash symbols from your file names and move on. I've done similar tasks before, renaming thousands of folders, and it didn't take long.

K Paul Over a year ago

Here's another link regarding renaming folders: superuser.com/questions/197380/…

YowE3K Over a year ago

FWIW - I assume the reason that a # can't be used is because that is the way that links jump to somewhere part-way through a webpage, i.e. a "bookmark".

K Paul Over a year ago

@YowE3K, you may be right, but you'd think they would have incorporated that behavior when you open a URL.

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related