I have an Excel file with urls in consequetive cells in a column. I want to
place the url information contained in the hyperlink in the cell next to the
hyperlink (the url is the info. that can be seen as you hover the mouse over
the hyperlink). The hyperlinks are dynamic and change regularly (almost
daily). Therefore the url information changes daily.

Things i have tried:
1. Use the edit hyperlink feature - copy the hyperlink and paste it in the
adjacent cell.
2. Use macro to automate the above action

The problem is:

The "copying of hyperlink" in step 1 is recorded as hard copy in the macro.
Hence, each time I run the macro, it just pastes the hard coded url in the
cell. It does not go to the source cell, copies the url in the cell and then
paste it. As I mentioned above, the hyperlink and therefore the urls in them
change frequently (almost daily). Therefore I need the copy function to copy
the url in the hyperlink at that time.

Your assistance is highly appreciated.

Thanks,

DM

Saved from a previous post:

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dealmessenger wrote:

I have an Excel file with urls in consequetive cells in a column. I want to
place the url information contained in the hyperlink in the cell next to the
hyperlink (the url is the info. that can be seen as you hover the mouse over
the hyperlink). The hyperlinks are dynamic and change regularly (almost
daily). Therefore the url information changes daily.

Things i have tried:
1. Use the edit hyperlink feature - copy the hyperlink and paste it in the
adjacent cell.
2. Use macro to automate the above action

The problem is:

The "copying of hyperlink" in step 1 is recorded as hard copy in the macro.
Hence, each time I run the macro, it just pastes the hard coded url in the
cell. It does not go to the source cell, copies the url in the cell and then
paste it. As I mentioned above, the hyperlink and therefore the urls in them
change frequently (almost daily). Therefore I need the copy function to copy
the url in the hyperlink at that time.

Your assistance is highly appreciated.

Thanks,

DM

--

Dave Peterson

Thanks Dave....I did some research and have come aross the url you mentioned.
This is a fabulous community.

Cheers

"Dave Peterson" wrote:

Saved from a previous post:

One way to extract those URL's from a hyperlink created via Insert|Hyperlink
is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Be aware that if you change the hyperlink, then this formula cell won't change
until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dealmessenger wrote:

I have an Excel file with urls in consequetive cells in a column. I want to
place the url information contained in the hyperlink in the cell next to the
hyperlink (the url is the info. that can be seen as you hover the mouse over
the hyperlink). The hyperlinks are dynamic and change regularly (almost
daily). Therefore the url information changes daily.

Things i have tried:
1. Use the edit hyperlink feature - copy the hyperlink and paste it in the
adjacent cell.
2. Use macro to automate the above action

The problem is:

The "copying of hyperlink" in step 1 is recorded as hard copy in the macro.
Hence, each time I run the macro, it just pastes the hard coded url in the
cell. It does not go to the source cell, copies the url in the cell and then
paste it. As I mentioned above, the hyperlink and therefore the urls in them
change frequently (almost daily). Therefore I need the copy function to copy
the url in the hyperlink at that time.

Your assistance is highly appreciated.

Thanks,

DM

--

Dave Peterson