Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 "JLatham" wrote: 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. "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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" wrote: 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. "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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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" wrote: 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" wrote: 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. "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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlinks imported into Excel
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. "MarkB" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert imported Access Hyperlinks to Excel and KEEP the li | Excel Worksheet Functions | |||
Activate email hyperlinks in imported data | Excel Discussion (Misc queries) | |||
autocreating hyperlinks in excel? ie 500 cells to 500 hyperlinks? | Excel Programming | |||
Update 2000 Excel hyperlinks to 2003 hyperlinks | Excel Worksheet Functions | |||
How toi turn-off hyperlinks [excel]? Email hyperlinks pop up ! | Excel Discussion (Misc queries) |