Extracting Url from a hyperlink
 

I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

Hi
see your post in Excel.misc
P.S.: please dont multipost

--
Regards
Frank Kabel
Frankfurt, Germany

"Carlton Noles" schrieb im Newsbeitrag
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

Thanks! Much Appreciated.
-----Original Message-----
i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel
function?


.

Extracting Url from a hyperlink
 

ok, but how do you insert the url nexto the cell where the url was found ?

henry

"Tom Ogilvy" wrote:

i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

Dim hlnk as Hyperlink, rng as Range
for each hlnk in ActiveSheet.Hyperlinks
set rng = hlnk.parent.offset(0,-1)
rng.Value = hlnk.Address
if len(trim(hlnk.SubAddress)) < 0 then
rng.Value = Trim(rng.value & " " & hlnk.SubAddress)
End if
Next

--
Regards,
Tom Ogilvy

"Henry" wrote in message
...

ok, but how do you insert the url nexto the cell where the url was found ?

henry

"Tom Ogilvy" wrote:

i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

does this look ok for you ? it works fine though.

Dim myLnk As Hyperlink
For Each myLnk In ActiveSheet.Hyperlinks
Cells(myLnk.Parent.Row, 6).Value = myLnk.Address
Next myLnk

maybe this mor compact code ?

henry

"Tom Ogilvy" wrote:

Dim hlnk as Hyperlink, rng as Range
for each hlnk in ActiveSheet.Hyperlinks
set rng = hlnk.parent.offset(0,-1)
rng.Value = hlnk.Address
if len(trim(hlnk.SubAddress)) < 0 then
rng.Value = Trim(rng.value & " " & hlnk.SubAddress)
End if
Next

--
Regards,
Tom Ogilvy

"Henry" wrote in message
...

ok, but how do you insert the url nexto the cell where the url was found ?

henry

"Tom Ogilvy" wrote:

i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?

Extracting Url from a hyperlink
 

ok, but how do you insert the url nexto the cell where the url was found ?

If the hyperlink is next to column 6, then that was information you didn't
provide. If the address property is all that you want, I can't guess that
either.

Unfortunately, I can't read your mind.

--
Regards,
Tom Ogilvy


"Henry" wrote in message
...
does this look ok for you ? it works fine though.

Dim myLnk As Hyperlink
For Each myLnk In ActiveSheet.Hyperlinks
Cells(myLnk.Parent.Row, 6).Value = myLnk.Address
Next myLnk

maybe this mor compact code ?

henry

"Tom Ogilvy" wrote:

Dim hlnk as Hyperlink, rng as Range
for each hlnk in ActiveSheet.Hyperlinks
set rng = hlnk.parent.offset(0,-1)
rng.Value = hlnk.Address
if len(trim(hlnk.SubAddress)) < 0 then
rng.Value = Trim(rng.value & " " & hlnk.SubAddress)
End if
Next

--
Regards,
Tom Ogilvy

"Henry" wrote in message
...

ok, but how do you insert the url nexto the cell where the url was
found ?

henry

"Tom Ogilvy" wrote:

i = 1
for each hlnk in ActiveSheet.Hyperlinks
Cells(i,1).Value = hlnk.Address
Cells(i,2).Value = hlnk.SubAddress
i = i + 1
Next

--
Regards,
Tom Ogilvy


"Carlton Noles" wrote in message
...
I have a large list of hyperlinks I have imported to
Excel. I need to get the urls these links point to. Is
there a way to do this through code or an Excel function?