| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting URL from Hyperlink | Excel Discussion (Misc queries) | |||
| Can't make hyperlink function work for hyperlink to website | Excel Worksheet Functions | |||
| Extracting hyperlink from a cell | Excel Worksheet Functions | |||
| Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
| reading html when hyperlink address not hyperlink text diplayed | Excel Programming | |||
Linear Mode
