ExcelBanter - Extracting Hyperlinks

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Extracting Hyperlinks (https://www.excelbanter.com/excel-programming/347791-extracting-hyperlinks.html)

Extracting Hyperlinks

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

Extracting Hyperlinks

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
'<<=============

---
Regards,
Norman

"Steven Drenker" wrote in message
. ..
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

Extracting Hyperlinks

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

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
'<<=============

---
Regards,
Norman

"Steven Drenker" wrote in message
. ..
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

Extracting Hyperlinks

Hi Steve,

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

http://tinyurl.com/adkp9

---
Regards,
Norman

"Steven Drenker" wrote in message
...
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

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
'<<=============

---
Regards,
Norman

"Steven Drenker" wrote in message
. ..
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

Extracting Hyperlinks

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

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=492382

Extracting Hyperlinks

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

---
Regards,
Norman

"Steven Drenker" wrote in message
...
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

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
'<<=============

---
Regards,
Norman

"Steven Drenker" wrote in message
. ..
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