Extracting Hyperlinks

[Steven Drenker]

I've got a Mac, OS X 10.3.9, Excel X, SR 1.

My spreadsheet has a column of values with Hyperlinks. How do I extract the
URL from the Hyperlinks and put the URL in the column to the right of the
Hyperlink?

TIA
Steve

 

[Norman Jones]

Hi Steven,

Try:

'=============>>
Sub Tester()
Dim rng As Range
Dim hLink As Hyperlink

For Each hLink In ActiveSheet.Hyperlinks
hLink.Parent(1, 2).Value = hLink.Address
Next hLink

End Sub
'<<=============

 

[Steven Drenker]

Hi, Norman. Thanks for the tip. Unfortunately, on my Excel Mac version, the
properties of HyperLink don't seem to be working:



Sub Tester()
Dim rng As Range
Dim hLink As Hyperlink

For Each hLink In ActiveSheet.Hyperlinks
Debug.Print hLink.Parent.Address
Debug.Print "Hyperlink Address " & hLink.Address
Debug.Print "Hyperlink Application " & hLink.Application
Debug.Print "Hyperlink Creator " & hLink.Creator
'Debug.Print "Hyperlink EmailSubject " & hLink.EmailSubject
Debug.Print "Hyperlink Name " & hLink.Name
Debug.Print "Hyperlink Parent " & hLink.Parent
Debug.Print "Hyperlink Range " & hLink.Range
Debug.Print "Hyperlink ScreenTip " & hLink.ScreenTip
'Debug.Print "Hyperlink Shape " & hLink.Shape
Debug.Print "Hyperlink SubAddress " & hLink.SubAddress
Debug.Print "Hyperlink Type " & hLink.Type


hLink.Parent(1, 2).Value = hLink.Address
Next hLink

End Sub


Here are the results:
$A$1
Hyperlink Address
Hyperlink Application Microsoft Excel
Hyperlink Creator 1480803660
Hyperlink Name
Hyperlink Parent Yahoo
Hyperlink Range Yahoo
Hyperlink ScreenTip
Hyperlink SubAddress
Hyperlink Type 0

Address, Name and SubAddress properties are empty. Parent and Range
properties are wrong.

What's wrong here?

Steve

 

[Norman Jones]

Hi Steve,

See the following post on this subject from JE McGimpsey in the
microsoft.public.mac.office.excel group:

http://tinyurl.com/adkp9

 

[Leith Ross]

Hello Steve,

Try this code...

Sub ExtractURLs()

Dim Rng As Excel.Range
Dim HypLnk As Variant

For Each HypLnk In ActiveSheet.Hyperlinks
Set Rng = HypLnk.Range
Rng.Offset(0, 1).Value = HypLnk.Address
Next HypLnk

End Sub


Sincerely,
Leith Ross

 

[Steven Drenker]

Thanks, Norman. As I suspected, it's a bug in Excel and I need to upgrade to
Excel 2004.
Steve

---------

Hi Steve,

See the following post on this subject from JE McGimpsey in the
microsoft.public.mac.office.excel group:

http://tinyurl.com/adkp9