Copy & Paste Hyperlink address into cell

R

Ron van Oijen

Hi,

I have downloaded a huge list of rock artist names together with their
musical styles into an MS Excel 2003 worksheet. Each name has a hyperlink
attached to it, and clicking the hyperlink will open a web page with
detailed information about this artist. For database purposes, the hyperlink
is extremely useful for identifying the artist, as each hyperlink contains a
number that is unique for that artist. So getting the URL value of the
hyperlink and extracting this number from it will give me the means to
differentiate between artists with identical names rather than to confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel > open the Edit Hyperlink window > copy the
hyperlink-address > close the Edit Hyperlink window > paste into the cell.
This will fully display the URL value of the hyperlink. But this is not
practical for my situation, where I'd have to repeat this for about 50,000
times. Using the Excel Macro Recorder I have tried to create a macro that
will copy and paste the hyperlink into the cell to the right of the cell
with the original artist data, using the sequence of the manual procedure.
The macro fails, however, as the macro-recorder doesn't record the action of
copying the hyperlink-address during the opening of the Edit Hyperlink
window; so it will paste any content that happens to be on the Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me find a way
to solve this little problem.

Ron.
 
F

Frank Kabel

Hi
could you give some examples for your hyperlinks. That is if you want
to extract this number at which position is this number stored?
 
R

Ron van Oijen

Hi Frank,

Perhaps I haven't stated the problem as clearly as I should have done. The
artist name is the only text that is visible in the cell. The hyperlink is
attached to the artist name, but invisible. It becomes only visible when
placing the mouse pointer over the cell, or when opening the Edit Hyperlink
window. Spreadsheet functions don't seem to be able to manipulate
hyperlinks, as the hyperlink isn't really a part of the cell contents. The
hyperlink seems to behave more like an attachment to the cell contents. The
only way to get a grip on the hyperlink code seems to be by using the Edit
Hyperlink window, which doesn't feature something like a built-in "copy
hyperlink as text" function. So the real problem is how to convert the
hyperlink to "normal text". After this step has been taken, the usual text
functions can be applied.

To give some real examples:

The only thing visible in a cell is the artist name, so this is how the
contents of a cell look like:

Fifty Foot Hose


This is the hyperlink that is attached to this name:

http://www.mmguide.musicmatch.com/artist/artist.cgi?ARTISTID=783780

Remember that this is not "normal text" but hypertext.

There is also a menu option in Excel: Edit > Paste as Hyperlink, but this
option doesn't seem to do anything special as compared to the normal Paste
option. Applied to my artist data it only copies the cell contents and
doesn't convert the hypertext to normal text.

I somehow get the feeling that perhaps the Edit Hyperlink window should be
extended with a "copy hyperlink as text" function. Is such a thing possible
in Microsoft Excel 2003?

Regards,
Ron
Groningen, The Netherlands
 
D

David McRitchie

Hi Ron,

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(A2)
=personal.xls!hyperlinkaddress('links sheet'!A2)


so you might use continuing from Frank's example, something like

=MID(personal.xls!hyperlinkaddress(A2),FIND("=")+1,20)
or
=IF(personal.xls!hyperlinkaddress(A2)="", "", personal.xls!hyperlinkaddress('links sheet'!A2))



More information on Hyperlinks on
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
 
R

Ron van Oijen

Hi David,

Thanks for the suggestion. I'm afraid that I'm still very new to Visual
Basic programming, so I think it's about time for me to start looking around
for some good introductory book on the subject. I have looked up the
internet site that you mention, and there I found your Visual Basic
function, where it is called "Function to show hyperlink URL used in another
cell (#URL)" / "Hyperlink Address (#Hyperlinkaddress)". This sounds great to
me, a function that will render the hyperlink visible seems to be exactly
what I need. But the code itself still looks a bit like abacadabra to me and
because I suspect I shall have to adjust the generic code to my specific
situation, I think I shall take a look around in the library.

Thanks again,
Ron,
Groningen, The Netherlands.


David McRitchie said:
Hi Ron,

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(A2)
=personal.xls!hyperlinkaddress('links sheet'!A2)


so you might use continuing from Frank's example, something like

=MID(personal.xls!hyperlinkaddress(A2),FIND("=")+1,20)
or
=IF(personal.xls!hyperlinkaddress(A2)="", "",
Click to expand...
personal.xls!hyperlinkaddress('links sheet'!A2))
 
D

David McRitchie

Hi Ron,
You posted in programming so I assumed you are familiar with both
Subroutines and Functions. If not see my pages
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

Macros and Functions are installed in the same manner.

For user defined functions (UDF) , you have to include the workbook
name if they are not in the same workbook.

Macros can be used from the same workbook or from any open
(but hidden) workbook, without specifically including the workbook
name that the macro resides in.

You should be able to use a macro or function supplied for your
request whether you understand it or not. Unless you have
to translate the names of the functions. I'll copy this to
Ron de Bruin in case that is a possible problem.

There are some references to VBA tutorials on my page
http://www.mvps.org/dmcritchie/excel/excel.htm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
 
R

Ron van Oijen

Hi David,

Wow! You seem to be a real VBA-expert when looking at your pages at
mvps.org. There seems so much that is covered on these pages. I'm quite
familiar with using Excel when it comes down to using the built-in
functionality or building macros using the macro-recorder, but I've never
moved beyond that phase. But the last several months I've had several
occasions where I thought that perhaps I should start mastering Visual Basic
for extending the functionality of the program. For the most part this
happened when I had to design some very simple macros or VBA program code
when using MS Access for designing databases. I do have some basic notion of
subroutines and functions, but I'm not really familiar with the use and
functionality of reserved words and such. So I'm very much aware of the fact
that I'm using Excel and Access on a rather basic level. I hope your Excel
pages will give me the jump-start that I need to move beyond that barrier.

The MS Office version that I use is the Dutch version, so translation of
function names or reserved words will be an issue.Can you tell me how I can
contact Ron de Bruin, if I need to?

Thanks again for the support!

Ron van Oijen
Groningen, The Netherlands
 
F

Frank Kabel

Hi
as an addition to David's link see Norman Harker's function list. You
can download it at:
http://www.contextures.com/functions.html

It also includes a Dutch translation (done by Ron de bruin)

--
Regards
Frank Kabel
Frankfurt, Germany

Ron van Oijen said:
Hi David,

Wow! You seem to be a real VBA-expert when looking at your pages at
mvps.org. There seems so much that is covered on these pages. I'm quite
familiar with using Excel when it comes down to using the built-in
functionality or building macros using the macro-recorder, but I've never
moved beyond that phase. But the last several months I've had several
occasions where I thought that perhaps I should start mastering Visual Basic
for extending the functionality of the program. For the most part this
happened when I had to design some very simple macros or VBA program code
when using MS Access for designing databases. I do have some basic notion of
subroutines and functions, but I'm not really familiar with the use and
functionality of reserved words and such. So I'm very much aware of the fact
that I'm using Excel and Access on a rather basic level. I hope your Excel
pages will give me the jump-start that I need to move beyond that barrier.

The MS Office version that I use is the Dutch version, so translation of
function names or reserved words will be an issue.Can you tell me how I can
contact Ron de Bruin, if I need to?

Thanks again for the support!

Ron van Oijen
Groningen, The Netherlands



David McRitchie said:
Hi Ron,
You posted in programming so I assumed you are familiar with both
Subroutines and Functions. If not see my pages
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

Macros and Functions are installed in the same manner.

For user defined functions (UDF) , you have to include the workbook
name if they are not in the same workbook.

Macros can be used from the same workbook or from any open
(but hidden) workbook, without specifically including the workbook
name that the macro resides in.

You should be able to use a macro or function supplied for your
request whether you understand it or not. Unless you have
to translate the names of the functions. I'll copy this to
Ron de Bruin in case that is a possible problem.

There are some references to VBA tutorials on my page
http://www.mvps.org/dmcritchie/excel/excel.htm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
Click to expand...
looking
around
Click to expand...
in
another
Click to expand...
sounds
great to abacadabra to me
and have
done.
Click to expand...
visible
when
Click to expand...
Click to expand...
 
R

Ron de Bruin

Hi Ron (nice name)

You have the answer from Frank and David I see.
If you need help post back.
 
R

Ron van Oijen

Hi Team!

I would like to thank David McRitchie, Frank Kabel and Ron de Bruin for the
generous help they gave me for solving my hyperlink problem. Because I'm a
novice to VBA, I lacked just about every knowledge about how to tackle this
little problem. But I'm learning...

After David sent me his solution in the form of a UDF, I even thought that
perhaps I would have to adapt this solution to my own situation. So I
started doing some reading about what I would be supposed to change in the
code. Perhaps some words would require translation, or perhaps the cell
referencing mode might have to be adjusted. But the code didn't contain any
existing function names, so I thought that there would probably be no
translation problem. And then suddenly I realised that a function really
must be a flexible piece of code that shouldn't require any further
modification. After all, I don't have to modify any of the regular Excel
functions either! They're supposed to work unconditionally. So then I went
on to make my first UDF using the VBA editor. I simply copied & pasted the
whole text into the editor sheet and saved it. Then I went for the Insert
Function button, and there it was: the HyperlinkAddress UDF. And did it
work!

I hope that some day I can do something in return. But that will require a
lot of study, I guess.

Thanks, guys!

Ron van Oijen,
Groningen, The Netherlands.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

hyperlink copy paste 3
Finding name of shape that was last clicked - Application.Caller 5
hyperlink address 1
Excel Macro to hyperlink an archived worksheet to an index page 0
Fire event code with Hyperlink and copy/paste RadioButtons 3
add hyperlink without changing the cell formating 6
HELP! 6
Hyperlink Cell to Shape on seperate worksheets 3

Top