hyperlink in a Excel from VBA macro

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

Answer 1

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

Answer 2

Hi Bernie,

 

It works! Thank You so much