Convert Salesforce id and name to clickable hyperlinks in an Excel table after extracting data from Power Query
firstname.lastname@example.org wrote this almost 4 years ago and it was last updated almost 4 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
Sub RemoveHyperlinks() Activesheet.Hyperlinks.Delete End Sub