Copying Hyperlink URL in VBA 1

[duncansancho]

Hello All,
I need to copy lists of hyperlinks to the adjacent cells, but as the URL addresses. When I use the macro recorder there is no difference between copying the activecell, and copying the Hyperlink - which is what I need and which can be done manually. This means that running the macro results in copying the the original phrase and not the URL!
Any ideas please!?
Duncan

 

[Loomah]

Duncan
By way of example this should return the URL as text

Range("a1").Hyperlinks(1).Address

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[PHV]

Something like this ?
MsgBox ActiveCell.Hyperlinks(1).Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[duncansancho]

Thanks for your reply Loomah. What is the (1) please, and where do I put the cell to copy into? (In the case you use it would be B1)
Duncan

 

[Loomah]

Duncan
It's just an index of the hyperlinks collection which seems a bit strange as I'm sure you can't have more than 1 hyperlink in a cell!

A fully example of what you need could be

Sub s()
Dim i As Integer
For i = 1 To 2 'or however many links you have
Cells(i, 2) = Cells(i, 1).Hyperlinks(1).Address
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[duncansancho]

Hi Loomah and PH,
I hope you guys are still there. Thanks for your previous replies - I was taken off to hospital just after and have only just returned. The info is fine but I need to maintain the address as a hyperlink, not as text only as is happening. Any Ideas?
Duncan

 

[TonyJollans]

Hi duncansancho,

I'm not entirely sure what your problem was in the first place - what went wrong when you recorded the process manually and then played back the macro? Do you want to copy cell contents, only hyperlinks, hyperlinks with or without displayed text which may differ from the underlying URL, or what?



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[duncansancho]

Hi Tony thanks for your interest, but I think I now have the answer! What I was trying to do was copy the adress of a hyperlink to the adjacent cell, but as a hyperlink also.
When I first tried recording the process and ran the resulting macro I got an exact copy of the copied cell rather than the address which can be done manually. The code which does the trick is as follows:-
ActiveCell.Offset(0, 1) = ActiveCell.Hyperlinks(1).Address

Duncan

 

[TonyJollans]

Hi Duncan,

I guess what I don't follow is that the solution(s) don't seem to match the stated problem, and ..

[blue][tt] ActiveCell.Offset(0, 1) = ActiveCell.Hyperlinks(1).Address[/tt][/blue]

.. will not copy a hyperlink, only the text of the URL (and possibly not even all of that).

If it helps, this should do what I think you want ..

[blue]Dim h As Hyperlink
 
ActiveCell.Copy
ActiveCell.Offset(, 1).PasteSpecial xlPasteAll
Application.CutCopyMode = False

For Each h In ActiveCell.Offset(, 1).Hyperlinks: With h
    .TextToDisplay = .Address & IIf(.SubAddress = "", "", IIf(.Address = "", "", " - ") & .SubAddress)
End With: Next[/blue]

(replace Activecell with whatever range you want to work on)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[duncansancho]

Thanks Tony - I ran it again and you are right. With a bit of adapting to my purposes it now works well!
Duncan