? to change long list of Hyperlinks to URL's?

Posted by Mike G on November 08, 2001 6:29 PM

I have a list of several hundred hyperlinks (with aliases?) that I'd like to be able to somehow display their URL addresses. I know that I can rightclick, hyperlink, copy hyperlink, and then paste. But, that works only for one cell at a time. With such a large group, how can I do all of these in a "group" type operation??

Posted by giacomo on November 08, 2001 9:15 PM

try this:

press alt+F11, then Insert|Module, paste this into the module:

Function getURL(alias)
getURL = alias.Hyperlinks.Item(1).Address
End Function

"alias" is the cell with the hyperlink. I'm assuming you only wanted the URL as text and not have it be a hyperlink as well. If this is not the case let me know.

giacomo

Posted by Mike G on November 09, 2001 6:02 PM

Thanks Giacomo,

Thanks for your help. Unfortunately, due to my own VB ineptitude, I couldn't fully figure out the instructions you suggested to work. Another set of instructions I received however I did get to work. If your interested, here's what I used.

Public Function GetAddressLink(Rng As Range)
GetAddressLink = ""
For Each Hl In ActiveSheet.Hyperlinks
Set i = Intersect(Hl.Range, Rng)
If i Is Nothing Then
Else
GetAddressLink = Hl.Address
Exit For
End If
Next
End Function

- Open your workbook
- Press Alt+F11 keys
- Insert a Module (Insert Menu>Module command)
- Paste the above code
- If an hyperlink stay on A1, on B1 insert =GetAddressLink(A1)
- Then fill down