Get Underlying Hyperlinks Out of Friendly Names

**Anne Troy** · 05-25-2004, 03:50 PM

Column A contains hyperlinks underneath other text.
How can I pull the hyperlinks into column B?

And then, what if I also wanted to trash the underlying hyperlinks in Column A?

**Zack Barresse** · 05-25-2004, 03:58 PM

maybe something like this??...

Sub transfer()
Dim rng As Range
    Set rng = Range("A1:A10")
    rng.Copy
    Range("B1:B10").PasteSpecial (xlPasteAll)
    With rng
    .Hyperlinks.Delete
    End With
End Sub

change ranges to suit. (if i understood correctly).

**Zack Barresse** · 05-25-2004, 04:15 PM

ok, how about this:

Sub transfer()
Dim rng As Range
    Set rng = Range("A2", Range("A2").End(xlDown))
    rng.Copy
    rng.Offset(0, 1).PasteSpecial (xlPasteAll)
    With rng
    .Hyperlinks.Delete
    End With
    Application.CutCopyMode = False
End Sub

better?

or will there possibly be non-contiguous ranges wanted with this? if so, how about:

Sub transfer()
Dim rng As Range
    Set rng = Range("A2", Range("A65536").End(xlUp))
    rng.Copy
    rng.Offset(0, 1).PasteSpecial (xlPasteAll)
    With rng
    .Hyperlinks.Delete
    End With
    Application.CutCopyMode = False
End Sub

this better? or am i shootin blanks here?

**Zack Barresse** · 05-25-2004, 04:33 PM

Originally Posted by Dreamboat

Okay...bad, bad me.

damn straight...

**Mark O'Brien** · 05-25-2004, 08:41 PM

How about this for being more generic?

The benefit of this method is that neither range is hard-coded.

Public Sub example()
'This subroutine acts as an example of how to use the "target" subroutine
  Dim oRange As Range
  Dim oTarget As Range
Set oRange = Range("A2", Range("A65536").End(xlUp))
  Set oTarget = oRange.Offset(0, 1)
  transfer oRange, oTarget
End Sub
Private Sub transfer(ByRef rng As Range, ByRef Target As Range)
'This is the actual solution to the post
  rng.Copy
  Target.PasteSpecial (xlPasteAll)
  With rng
    .Hyperlinks.Delete
  End With
  Application.CutCopyMode = False
End Sub

**TonyJollans** · 05-26-2004, 02:12 AM

I think the 'problem' needs a bit more definition. Originally it was column A to column B; now it's variable (User-supplied) ranges - big difference

.

Is this to run from the User Interface - in other words is the User going to specify the source range - by selection, let's say, before clicking a button. And if so, how is the user going to specify the target, or should it always be the column to the right - or a new column? What if the source range is multicolumn - should new columns (if wanted) be added as a block, or interleaved? If a single column goes to the column on the right, should, say, a single row selection go to the row BELOW?

What should happen to non-hyperlink data in the source range? What should happen to existing data in a (User-selected) target range?

I do think it's reasonable to insist on a single contiguous range.

Ignoring selections for a minute, one point about all the code offered so far is that it doesn't separate out the hyperlinks from the friendly names which seems to be what was asked for. I think this requires spinning through the Hyperlinks Collection.

**Mark O'Brien** · 05-26-2004, 06:17 AM

Public Sub main()
Dim oRange As Range
'The rest of this code will be green.
'and formatting not preserved.
'(most notably when you edit the post)
MsgBox "hi"
Set oRange = ActiveSheet.Range("A1")
End Sub

Edit by mark007: hmm, I saw a post like this before and when I went to edit it it looked fine then when I saved changes it looked fine. I'm not sure what the issue is. Does it happen everytime you post?

EDIT:: OS is Win2K. Yes, when I editted the post previously it jacked up the spacing, but now it seems to work.

BTW, for clarity it's probably better to reply to a post like this rather than to edit it.

**XL-Dennis** · 05-26-2004, 06:41 AM

What code is going to be the easiest to use and implement for the average person.

I always try to target the KISS-concept:
Keep it simple, stupid (for the end-users)

An add-in that is automatically activated upon installation
where the function is available via a toolbar button and/or menu-option and
where the end-users select the source-range (before hitting the button)
and then via a small inputbox select the first target-cell.

Based on this approach we can start to write the code as we have located the functionality and a userfriendly interface.

Do we have green light?
Dennis

**TonyJollans** · 05-26-2004, 07:32 AM

Dennis,

I agree 100% - if it ain't simple to use it won't get used.

I'll go with your definition. And ..

.. overwrite cells in target range
.. copy non-hyperlinks in range unchanged?
.. disallow overlapping ranges?

Anybody else?

**XL-Dennis** · 05-26-2004, 09:50 AM

Tony,

I?m with You -

Kind regards
Dennis

**Zack Barresse** · 05-26-2004, 10:05 AM

Originally Posted by Dennis W

Tony,

I?m with You -

Kind regards
Dennis

ditto - Dennis

**Mark O'Brien** · 05-26-2004, 11:00 AM

I guess I'm following the UI discussion here. I editted firefytr's code with a suggestion to make the subroutine generic, I was not trying to address the initial problem:

Private Sub transfer(ByRef rng As Range, ByRef Target As Range)
'This is the actual solution to the post
  rng.Copy
  Target.PasteSpecial (xlPasteAll)
  With rng
    .Hyperlinks.Delete
  End With
  Application.CutCopyMode = False
End Sub

You can copy this code in anywhere and call it from a main subroutine. The GUI doesn't matter as much as the code interface. The subroutine as I posted becomes an encapsulated, standalone routine. You pass two ranges and the routine does the rest.

I provided an example subroutine as to how you could use it. I don't think it really matters howthe GUI is set up since that just creates more overhead on the forum. i.e. it doesn't matter if the OP wants to create an add-in or a userform or use a intputbox to get the ranges. If the OP needs that help, then they will ask.

Or am I missing something here?

**TonyJollans** · 05-27-2004, 02:41 AM

Yes, Mark, the UI and the functionality are different beasts - it was Anne's second post that I thought brought the UI into scope, and there is probably a bigger issue here to do with run instructions.

If I were an ignorant user (some say I am, of course

) how could I take a KB solution and use it? Posting detailed instructions with every piece of code is way over the top; should there be some generic instructions somewhere so that, in an individual case it's sufficient to say something like - paste this code into a standard code module (or ThisWorkbook, or wherever), close the VBA, etc. - which can then be referred to?

Anyway, in this instance, none of the code posted so far has separated out the underlying hyperlinks, so here's some code which does ..

Private Sub transfer(ByRef rng As Range, ByRef Target As Range)
Dim h As Hyperlink
rng.Copy
Target.PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
With rng
    .Hyperlinks.Delete
End With
For Each h In Target.Hyperlinks
    h.TextToDisplay = h.Address & IIf(h.SubAddress = "", "", IIf(h.Address = "", "", " - ") & h.SubAddress)
Next
End Sub

This could be used with something like ..

Sub TestIt()
' Get SourecRange from User
' Get TargetRange from User
transfer SourceRange, TargetRange
End Sub

where Getting the ranges from the User are (probably) other KB code routines.

**TonyJollans** · 05-27-2004, 02:54 AM

Well, well, well - I didn't tag my second piece of 'code' but the system identified it as VBA - very clever!

Thread: Get Underlying Hyperlinks Out of Friendly Names

Thread Tools

Display

Get Underlying Hyperlinks Out of Friendly Names

Posting Permissions