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.

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?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
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.

Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
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.

Hi
try in an adjacent cell
=MID(A1,FIND("=")+1,20)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the
number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
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.

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/ar...RTISTID=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



"Frank Kabel" schreef in bericht
...
Hi
try in an adjacent cell
=MID(A1,FIND("=")+1,20)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the
number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
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?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
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.

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

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" schreef in bericht
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

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...tm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote in message i.nl...
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" schreef in bericht
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

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" schreef in bericht
...
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...tm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote in message
i.nl...
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" schreef in bericht
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

Hi Ron,
I guess this what I was thinking of but I think you'll hear from Ron de Bruin,
he's a regular in these newsgroups..

MS Excel: Analysis ToolPak Translator 7.0
http://www.acoustics-noise.com/ATPtranslator.shtml

Eric Desart with Jurgen aka KeepItCool, Ron de Bruin, Laurent Longre,
Jan Karel Pieterse, Roland Greve

I think it is based on Ron's material on John Walkenbach's site.
Help Context IDs
http://j-walk.com/ss/excel/tips/tip89.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


"Ron van Oijen" wrote ...

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

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" schrieb im Newsbeitrag
i.nl...
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" schreef in bericht
...
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...tm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote in message
i.nl...
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" schreef in bericht
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov.
2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

Hi Ron (nice name)

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron van Oijen" wrote in message i.nl...
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" schreef in bericht
...
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...tm#vbtutorials

Original question for this thread was posted 4hours 42 minutes
before this reply. (for Ron de Bruin's benefit)
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote in message
i.nl...
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" schreef in bericht
...
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
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

I think it is based on Ron's material on John Walkenbach's site.
Help Context IDs
http://j-walk.com/ss/excel/tips/tip89.htm

I have two new files for 2000-2003
But this is only English

http://www.rondebruin.nl/id.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"David McRitchie" wrote in message ...
Hi Ron,
I guess this what I was thinking of but I think you'll hear from Ron de Bruin,
he's a regular in these newsgroups..

MS Excel: Analysis ToolPak Translator 7.0
http://www.acoustics-noise.com/ATPtranslator.shtml

Eric Desart with Jurgen aka KeepItCool, Ron de Bruin, Laurent Longre,
Jan Karel Pieterse, Roland Greve

I think it is based on Ron's material on John Walkenbach's site.
Help Context IDs
http://j-walk.com/ss/excel/tips/tip89.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


"Ron van Oijen" wrote ...

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

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.


"David McRitchie" schreef in bericht
...
Hi Ron,
I guess this what I was thinking of but I think you'll hear from Ron de
Bruin,
he's a regular in these newsgroups..

MS Excel: Analysis ToolPak Translator 7.0
http://www.acoustics-noise.com/ATPtranslator.shtml

Eric Desart with Jurgen aka KeepItCool, Ron de Bruin, Laurent Longre,
Jan Karel Pieterse, Roland Greve

I think it is based on Ron's material on John Walkenbach's site.
Help Context IDs
http://j-walk.com/ss/excel/tips/tip89.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm


"Ron van Oijen" wrote ...

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