1

In Excel, I'm trying create hyperlinks that display the unicode folder icon ( as well as various other icon). I can do this manually by right clicking --> Hyperlink --> and then "cut and paste" the character from the web browser. My problem is I'm trying to add the hyperlinks with VBA, but I'm not sure how to do it. Here's the code I'm trying, but I just get an error. You can see that in my code to create the hyperlink I'm using the ChrW funtion with TextToDisplay:=ChrW(&H1F4C1). Unfortunately Excel throws an error when it get to that line.

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=ChrW(&H1F4C1)

enter image description here

Frank
  • 459
  • 1
  • 5
  • 13

1 Answers1

1

There is an upper limit on the character code accepted by the ChrW function (see MSDN documentation).

The normal range for charcode is 0–255. However, on DBCS systems, the actual range for charcode is -32768–65535.

In you case the character code for the folder symbol is 128193 so it's out of scope for this function.

Taking one of the suggested answers to this question (from Mark Tolonen), your code could be the following (if you are running Excel 2013 or higher):

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=WorksheetFunction.Unichar(&H1F4C1)

If you are running Excel 2010 or lower, then you'd have to use one of the other answers provided in the question mentioned above. If you choose milevyo's answer which uses an html workaround, you would have to add the following:

Function GetUnicode(CharCodeString As String) As String
    Dim Doc As New HTMLDocument
    Doc.Body.innerHTML = "&#x" & CharCodeString & ";"
    GetUnicode = Doc.Body.innerText
End Function

and then you code would be:

'Purchase Orders Folder
    ActiveSheet.Range("ProjectTable[POs]").Cells(lastTblRowNum).Select                
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=poPath, TextToDisplay:=GetUnicode("1F4C1")

Also, make sure you add a reference to the Microsoft HTML Object Library.

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
  • 1
    As a further remark to the cited link [Get UniCode characters with CharCode values greater hex FFFF](https://stackoverflow.com/questions/56008191/get-unicode-characters-with-charcode-values-greater-hex-ffff): in addition to the shown worksheetfunction you find 2 approaches using *byte* operations, 2 are using *html* workarounds and one `WorkSheetFunction.FilterXML`. – T.M. Aug 04 '20 at 11:48
  • @DecimalTurn I'm trying your solution, but I'm getting a Run-timeerror '438': Object doesn't support this property or method – Frank Aug 04 '20 at 15:27
  • @Frank, what version of Excel are you using? – DecimalTurn Aug 04 '20 at 16:09
  • @DecimalTurn 2010 Professional – Frank Aug 04 '20 at 16:37
  • @Frank – The Unichar function is only available with Excel 2013 or higher on Windows, so I've edited my answer to provide an alternative. – DecimalTurn Aug 04 '20 at 21:28
  • @DecimalTurn - Yeah, I know I'm not doing myself any favors by using 2010, but that's what I'm stuck with in this situation. Great solution though. Thanks! – Frank Aug 05 '20 at 00:15