Convert Salesforce id and name to clickable hyperlinks in an Excel table after extracting data from Power Query
me@jaykilleen.com wrote this about 6 years ago and it was last updated about 6 years ago.
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