Neat Excel trick - Make your column of ID's into Links

(UPDATED 1/15/2012 - figured out more VB and added code for Queues)

This is a variation on a tip from Ron de Bruin (www.rondebruin.nl). He did the heavy lifting. I just substituted some text:

When importing a report into Excel, having the ID is very handy. You can then plunk it into your URL window, overwriting what's past "https://<>.salesforce.com/" to get to your record.

With this macro you can automatically add link info to your ID column so that a quick click on the cell opens up your default browser and gets you right to the record.

Here's a macro that does it for the current column. Just click a cell in the column and fire up the macro:


Sub ConvertToLinks()
'
' ConvertToLinks
'
' Based on guidance from
'    Ron de Bruin
'    http://www.rondebruin.nl
'
'
'
    For Each myCell In Columns(Selection.Column).Cells.SpecialCells(xlCellTypeConstants)
        If myCell.Value Like "00G*" Then
            ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
                Address:="https://na1.salesforce.com/p/own/Queue/d?id=" & myCell.Value, TextToDisplay:=myCell.Value
        Else
            ActiveSheet.Hyperlinks.Add Anchor:=myCell, _
                Address:="https://na1.salesforce.com/" & myCell.Value, TextToDisplay:=myCell.Value
        End If
    Next


End Sub


Just substitute your na's or cs's  where you see na1 and you're all set.

Also, note the If statement -- you can add more "Like" conditions as you come across more objects that don't follow the standard.

Thanks to Ron for the tip. (His original tip is below...)

-- David.

Re: Convert entire columns of text email addresses to hyperlinks

Try this one for Column B in the activesheet

Sub test()

For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)

If myCell.Value Like "*@*" Then

ActiveSheet.Hyperlinks.Add Anchor:=myCell, _

Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value

End If

Next

End Sub

--

Regards Ron de Bruin

http://www.rondebruin.nl

Pasted from <http://www.excelforum.com/excel-worksheet-functions/337315-convert-entire-columns-of-text-email-addresses-to-hyperlinks.html>

r