none
hyperlink in a Excel from VBA macro RRS feed

  • Question

  • Hi all,

    I'm trying to create a hyperlink in a Excel workbook from a VBA macro by the following statement: ActiveCell.Formula = "=HYPERLINK(""" & MyPath & MyFile & SN & "!A1" & """)" where MyPath, MyFile and SN are variables. I have to use the variables because hyperlink shall be created dynamically.

    When run the macro I get the right values assigned to the cell but Excel does not accept it as a Hyperlink: if I click in the cell I can see the single quote ' as the first character on the left and I can't delete it as well.

    Any suggestions on how to solve this?

    Thanks

    Thursday, August 11, 2011 9:51 PM

Answers

All replies

  • You can add the hyperlink like so:

        ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:= _
            MyPath & MyFile, SubAddress:="'" & SN & "'!A1", _
            TextToDisplay:="Click me!"

    I added single quotes to the sheet name in case it has spaces.

     


    HTH, Bernie
    Friday, August 12, 2011 1:30 AM
  • Hi Bernie,

     

    It works! Thank You so much

    • Marked as answer by VictL Tuesday, August 16, 2011 6:28 AM
    Friday, August 12, 2011 7:50 AM