1

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.

Community
  • 1
  • 1
CarloC
  • 155
  • 12

1 Answers1

1

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.

FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • My macro recorder gave me this: ActiveCell.FormulaR1C1 = "=HYPERLINK(AleksV!R[-15]C[-8],""2322 734 61009L"")". I'll try your option. – CarloC May 22 '15 at 19:29
  • My bad - you're using Excel _2007_. I'm running _2010_ - that's probably the difference. It may still work, though. – FreeMan May 22 '15 at 19:32
  • Your answer works minus the "[" & strThisBooksName & "]". It only needs the worksheet reference, not the book's. Thanks! – CarloC May 22 '15 at 19:38
  • My edit-comments-window lapsed so I'm adding that the ".Hyperlinks.Add" answer worked. – CarloC May 22 '15 at 19:45