Copy link's address

Question

How I can copy hyperlink's address into clipboard?

Using the "record macro" option, I get this:

Sub CopyHyperlink()
    Selection.Range.Hyperlinks(1).Range.Fields(1).Result.Select
    Selection.Copy
End Sub

But, this doesn't give me the desired result. See the picture to compare actual and desired results:

As you see, my code is actually copying link's text, instead of it's address.

Probably, there should be something like

Selection.Range.Hyperlinks(1).Address

but it doesn't work at all.

How make it work properly?

Step one, don't work with `Selection`. Step two, did you search in the *Object Browser* (F2) to see if there was a `Hyperlink` class, and what its members were? — Mathieu Guindon, Aug 28 '17 at 18:51
So, there you go. Where are you planning to paste it? If you mean to use it somewhere in your code, you can skip the whole entire clipboard/copy part. — Mathieu Guindon, Aug 28 '17 at 18:56
Read [how to avoid Select and Activate in VBA macros](https://stackoverflow.com/q/10714251/1188513) — Mathieu Guindon, Aug 28 '17 at 19:02
@Mat'sMug Thanks, very usuful. I'm new in VBA and to be honest, after JavaScript, it is very hard. — john c. j., Aug 28 '17 at 19:16

score 4 · Accepted Answer · answered Aug 28 '17 at 18:56

4

Assuming that you have used Selection because you recorded it with macro recorder, you should change it according to your workbook.

Insert the following library:

Tools -> References -> Microsoft Forms 2.0 Object Library

and use the following code:

Sub CopyHyperlink()
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject

clipboard.SetText Selection.Hyperlinks(1).Address
clipboard.PutInClipboard
End Sub

answered Aug 28 '17 at 18:56

Tehscript

2,556
2
13
23

2

Upvoted, although I suspect OP means to paste it somewhere in the workbook, which would make the whole clipboard thing moot. Just grab the address and write it wherever you need it. – Mathieu Guindon Aug 28 '17 at 18:58
@Mat'sMug thanks. Since OP mentions clipboard, I understood that he wants to copy it to clipboard, so that for instance he can use it in some other platform. If he simply wants to copy it, none of these are necessary as you said. – Tehscript Aug 28 '17 at 19:01
@Tehscript It works as expected, thank you very much. By the way, in my Word 2016 the library was missing in the menu (Tools > References). I used workaround [from here](https://stackoverflow.com/questions/35610429/why-do-i-not-see-the-microsoft-forms-2-0-object-library) - that's mean, click Insert > Userform. But I'm not sure it is the best and most accurate way. Another information could be found [here](https://stackoverflow.com/questions/35664768/cant-find-microsoft-forms-2-0-object-library-or-fm20-dll). – john c. j. Aug 28 '17 at 19:09

Copy link's address

1 Answers1