Hyperlinks imported into Excel

[MarkB]

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

 

[JLatham]

Are you sure things aren't working right? The formula looks good and should
work. Could it be that the cells simply aren't displaying as a hyperlink
(blue underlined text)? If you click the cell initially, does the link work
or not? Should even if not displayed as blue underlined text. If it's just
an appearance thing, you can select them all and set to blue and underlined.

 

[JLatham]

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]

JLatham,

Thanks for responding... When you click on the cell with the formula below
it does nothing. You actually have to select the cell and then click in the
formula bar, hit return and then the conversion works. (after you change the
formatting for the column of data to general). Rather odd! I'll try your code
from the next posting tonight and see how that works. I really appreciate
your help!

MarkB

 

[MarkB]

JLatham,

Is there something that I'm doing wrong with the export routine that I'm
using in sf.com to create the logic of this formula such that when imported
into excel it doesn't create the hyperlink automatically? It may be more
beneficial to fix that than to install a macro to do the conversion after the
spreadsheet is created.

Let me know you thoughts

Mark

 

[JLatham]

Right off the bat I can't imagine what you're doing wrong. But I'm not
familiar with 'sf.com' Have you tried formatting the cells in Excel as
General first, or do you have that opportunity (if sf.com is creating the
workbook, I guess you don't).

If you're creating new workbooks with this all the time, you could put the
code I wrote into your Personal.xls workbook, or into any workbook at all and
just open that workbook along with one of the created workbooks. You'd need
to change
Worksheets(linkSheetName).
to
ActiveSheet.
in the code and make sure the sheet with the =HYPERLINK( formulas in it is
active in the workbook when you run the macro. At least you'd only have to
put the code into one workbook that way rather than continually be adding it
to a number of workbooks.

 

[MarkB]

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]

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]

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

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".

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]

If you want, you can contact me at (remove spaces)
HelpFrom @ jlathamsite.com
and show me the output and give the details of the whole process. About the
only way I can think of that the hyperlink formula wouldn't automatically
trigger the proper display of things is if the cell(s) it is being written to
are formatted as TEXT and not as General.

I think the important part may be the actual process you go through to get
information out of Salesforce.com into an Excel workbook. Especially
explaining when/how the Excel workbook itself gets created - whether you open
one up in advance of the data being imported to it, or if the sf.com program
is actually creating it for you.

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

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".

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

:

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

:

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