JLatham,

Again, I'm wondering if there is something I could be doing on the export
end of things within Salesforce.com to allow the hyperlinks to show up
properly as the workbook is being created automatically by the report export
wizard. It's quite flexible. Is there a way I can show you what the output of
the wizard is? Seems to create a sortable list and can put the URL address
into a cell just fine but it doesn't finish the conversion to the clickable
link part...

MarkB

"JLatham" wrote:

To make the code more generic, change the code to this:

Sub HyperlinksFromFormula()
Const hlColumn = "C" ' change as needed
Const firstRow = 2 ' 1st possible row with HYPERLINK() formula
Const formulaPhrase = "=HYPERLINK("
Dim lastRow As Long
Dim hlCells As Range
Dim anyCell As Range
Dim hText As String
Dim hLink As String
Dim p1 As Integer
Dim p2 As Integer
Dim p3 As Integer
Dim p4 As Integer

lastRow = ActiveSheet.Range(hlColumn & _
Rows.Count).End(xlUp).Row - 1
Set hlCells = ActiveSheet.Range(hlColumn & _
firstRow & ":" & hlColumn & lastRow)
For Each anyCell In hlCells
If anyCell.HasFormula Then
If Left(anyCell.Formula, Len(formulaPhrase)) = formulaPhrase Then
'must dig out text and hyperlink
p1 = InStr(anyCell.Formula, Chr$(34)) ' position of 1st "
p2 = InStr(p1 + 1, anyCell.Formula, Chr$(34)) ' position of 2nd "
p3 = InStr(p2 + 1, anyCell.Formula, Chr$(34)) ' position of 3rd "
p4 = InStrRev(anyCell.Formula, Chr$(34)) ' position of last/4th "
hLink = Mid(anyCell.Formula, p1 + 1, p2 - p1 - 1)
hText = Mid(anyCell.Formula, p3 + 1, p4 - p3 - 1)
If hText = "" Then
hText = hLink
End If
anyCell.Formula = "" ' delete formula
ActiveSheet.Hyperlinks.Add Anchor:=anyCell, _
Address:=hLink, TextToDisplay:=hText
End If
End If
Next ' end of hlCells loop
End Sub

To put the code into a workbook:
Open the workbook and press [Alt]+[F11] to open the VB Editor.
Choose Insert | Module from the VBE menu
Copy the code and paste it into the module presented and then you can close
the VB Editor.
To run the code:
Select a sheet with a list on it (list needs to be in same column on all
sheets - column can be changed in the code). Then use Tools | Macro | Macros
to get the list available macros, select the proper macro and click the [Run]
key.

Yes, the friendly name will be displayed and it will be hyperlinked to the
URL. Essentially this is taking your formula and determining what the URL
and 'friendly name' is in it, then erasing the formula and the result is the
same as if you'd just typed in the friendly name and then used Insert
Hyperlink to link to the URL.

It's a slight modification of a routine I use here to track discussions I've
helped with at this site, but in my use I don't use the HYPERLINK() formula,
I just paste the URL of a discussion into a cell and later I run my version
of the code which converts all cells beginning with "http://" to a hyperlink,
with the original link text as the "friendly name".


"MarkB" wrote:

Couple of questions regarding the code you have written here...

- How do you trigger the macro to run?
- I have 2 sheet out of 5 or 6 in the workbook that I would like to do these
conversions within. Do I need to embed in each workbook?
- Will the "friendly name" part of the argument be preserved through the
conversion you software is making?
- Is this code that you have used for doing this kind of conversion work for
others? Seems that you have put some thought into it so I appreciate that!!!

MarkB

"JLatham" wrote:

Just in case, here's some code that will work through a column and look for
cells with =HYPERLINK( formula in them, and turn the formula into an actual
hyperlink with same displayed text as before. That might solve your problem.
Change sheet name, column and first possible row as needed. No need to ever
change anything again, since after altering a =HYPERLINK( formula, it won't
be there and future runs of the same macro won't affect the already converted
cells at all.

Sub HyperlinksFromFormula()
Const linkSheetName = "Sheet1" ' change to real sheet name
Const hlColumn = "C" ' change as needed
Const firstRow = 2 ' 1st possible row with HYPERLINK() formula
Const formulaPhrase = "=HYPERLINK("
Dim lastRow As Long
Dim hlCells As Range
Dim anyCell As Range
Dim hText As String
Dim hLink As String
Dim p1 As Integer
Dim p2 As Integer
Dim p3 As Integer
Dim p4 As Integer

lastRow = Worksheets(linkSheetName).Range(hlColumn & _
Rows.Count).End(xlUp).Row - 1
Set hlCells = Worksheets(linkSheetName).Range(hlColumn & _
firstRow & ":" & hlColumn & lastRow)
For Each anyCell In hlCells
If anyCell.HasFormula Then
If Left(anyCell.Formula, Len(formulaPhrase)) = formulaPhrase Then
'must dig out text and hyperlink
p1 = InStr(anyCell.Formula, Chr$(34)) ' position of 1st "
p2 = InStr(p1 + 1, anyCell.Formula, Chr$(34)) ' position of 2nd "
p3 = InStr(p2 + 1, anyCell.Formula, Chr$(34)) ' position of 3rd "
p4 = InStrRev(anyCell.Formula, Chr$(34)) ' position of last/4th "
hLink = Mid(anyCell.Formula, p1 + 1, p2 - p1 - 1)
hText = Mid(anyCell.Formula, p3 + 1, p4 - p3 - 1)
If hText = "" Then
hText = hLink
End If
anyCell.Formula = "" ' delete formula
Worksheets(linkSheetName).Hyperlinks.Add Anchor:=anyCell, _
Address:=hLink, TextToDisplay:=hText
End If
End If
Next ' end of hlCells loop

End Sub

"MarkB" wrote:

I have a CRM system that allows me to export information into excel. As a
part of the export process I'm having the program generate what I hoped was a
active hyperlink back to the specif record in the host application. I'm using
the "HYPERLINK" function but it doesn't result in displaying the active
hyperlink in the target cells. After export in excel I need to format the
cells to general and then to select each cell and hit enter in the formula
bar to convert it to a hyperlink. Way too tedeous! Is there a short cut or
macro that could do this conversion for me? Or is there a way that I could
set up the export routine logic differently? Below is an example of what is
exported in excel...

=HYPERLINK("https://na2.salesforce.com/0064000000907ry", "WRS - SOSCOE
Studio - Eng Serv for LOCI 2.1 / LOS-SE 5.0 (March)")

Thanks for your help!

MarkB