Excel Macro to include hyperlink to where file is saved

[texasguy66]

Hello everyone.

I have an excel spreadsheet and have a tab that generates an email of the information. Would like to include a link in the spreadsheet to where the file is saved at (on a network drive). The folders change frequently so I am unable to have a fixed location. How could I achieve this?

Thanks in advance.

 

[offthelip]

you haven't said where on the worksheet you want the link or what the workhseet is called so I assume A1 and "emailtab" adjust as necessary:

Sub test()

tt = ActiveWorkbook.Path
     With Worksheets("emailtab")
      .Hyperlinks.Add Anchor:=.Range("a1"), _
                        Address:=tt, _
                        TextToDisplay:=tt
      End With

End Sub

 

[texasguy66]

Thanks, it works great.

Is there a way to show the hyperlink as the word "link" versus it showing the file path?

 

[offthelip]

yes very easily:

tt = ActiveWorkbook.Path
     With Worksheets("emailtab")
      .Hyperlinks.Add Anchor:=.Range("a1"), _
                        Address:=tt, _
                        TextToDisplay:="Link"
      End With

 

[texasguy66]

Perfect. Thanks for your help.