New Year's Resolution - More Blogging!

Hello, Gentle Reader,

One of my resolutions for the New Year is to blog more.

We'll start off light, with one of my favorite Support stories -- and this actually happened to me when I was working as an Assistant Mgr of IT Ops back in the early 1990's. Don't be too impressed -- there were only three of us in the department. But the company itself was impressive -- a major concert house in NYC. I ran into several famous people in the elevator. Cool job!

One of our most common tasks was to replace broken keyboards. Easy, and a nice excuse for getting up an walking around a bit. So, one day, I get the usual call -- "My keyboard stopped working." Into the closet I go to get a spare keyboard, up the staircase, unplug the old, plug in the new. Done! I ask the user, "anything happen out of the ordinary?". "Nope -- it just stopped working. I don't understand it." So, I grab the old keyboard by one end, start walking, and just about a full cup's worth of old coffee pours out the other end. Really tough moment -- had to keep my dignity when my entire being just wanted to bust out laughing!

Wishing you all a Healthy & Happy New Year!

And do check out the only post I didn't delete from past blogging below. This has come in really handy!

Sincerely,

-- David.

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