VBA produced HYPERLINK formula doesn't actually create workable hyperlink

Question

I'm trying to generate a HYPERLINK formula via VBA but when I click on the resulting link I get an Excel error message "Cannot open the specific file." I tried this but same results.

This is my code to generate the link.

wksUsersSheet.Range(COMMENTARY_COL & lngRowNow).FormulaR1C1 = 
"=HYPERLINK(" & Chr(34) & "[" & strThisBooksName & "]" & 
strFoundMatchWorksheetName & "!" & strFoundMatchAddress & Chr(34) & "," 
& Chr(34) & rngReturnedMatchingPart.Value & Chr(34) & ")"

The immediate window displays this: =HYPERLINK("[UPN_Template_Wip]AleksV!$I$4","2322 734 61009L")

I've tried it with .Formula as well but the result is the same.

I'm using Excel 2007 in Windows 7. Am I missing something here 'cause it's all looking fine, just not working fine. Thanks.

FreeMan FreeMan · Accepted Answer · 2015-05-22T19:23:06

Using the macro recorder to create a hyperlink on a blank test sheet, I got:

    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Sheet1!A1", TextToDisplay:="HL Here"

Translating that to your specifics, I believe you should have:

wksUsersSheet.Hyperlinks.Add Anchor:=wksUsersSheet.Cells(lngRowNow, Commentary_Col), _
       Address:="", _
       SubAddress:="[" & strThisBooksName & "]" & strFoundMatchWorksheetName & "!" & _
                   strFoundMatchAddress, _
       TextToDisplay:=rngReturnedMatchingPart.Value

Based on your comment try this:

Convert your strFoundMatchAddress to a row number & a column number, I'll call them FoundMatchAddrRow and FoundMatchAddrCol

 ActiveCell.FormulaR1C1 = "=HYPERLINK(" & Chr(34) & "[" & strThisBooksName & "]" & _
strFoundMatchWorksheetName & "!R[" & FoundMatchAddrRow & "]C[" &  _
FoundMatchAddrCol & "],""" &  _
rngReturnedMatchingPart.Value & """)"

Note the use of "" generates a single " in your final string & reduces the concatenation and eliminates the need for Chr(34). It's not required, but it does reduce the typing.

VBA produced HYPERLINK formula doesn't actually create workable hyperlink

1 Answers