Add Hyperlinks to an Entire Column using VBA in Excel

Convert Salesforce id and name to clickable hyperlinks in an Excel table after extracting data from Power Query

me@jaykilleen.com wrote this over 6 years ago and it was last updated over 6 years ago.


← Back to the Posts

I whipped up this procedure in Excel VBA so that I could convert the output a values in a table (usually an id and a name column) from Power Query into a clickable HTML hyperlink so the user could quickly navigate to the records homepage in Salesforce.

I used this with an existing table which made the conversion of a few thousand records in a split second. Was fun.

Option Explicit

Sub AddHyperLinks()

  Dim x As Integer
  Dim y As Integer: y = get_last_row("Report", 1)
  
  Dim id As String
  Dim name As String
  
  For x = 9 To y
  
    id = Cells(x, 1)
    name = Cells(x, 2)
    
    With ThisWorkbook.Sheets("Report")
      .Hyperlinks.Add Anchor:=.Range("B" & x), _
      Address:="https://domain.my.salesforce.com/" & id, _
      ScreenTip:="GoTo " & name, _
      TextToDisplay:=name
    End With
  
  Next x

End Sub

Function get_last_row(sheetname As String, column_number As Integer) As Long

  get_last_row = Sheets(sheetname).Cells(Rows.Count, column_number).End(xlUp).row

End Function

You may want to delete all hyperlinks from the worksheet before you begin in case you have a table that is dynamically changing. In this case add the following to the macro and call it in the first step using call RemoveHyperlinks.

Sub RemoveHyperlinks()

  Activesheet.Hyperlinks.Delete

End Sub

Similar