[Robert in Brisbane]

I have a spreadsheet with many lines of data, and each line has an image with
a hyperlink embedded.
I want to be able to do a general copy and paste of JUST the hyperlink
information, into another column, but cannot seem to work out if it can be
done.

Any help would be appreciated.
Thanks


Program version: MS Excel 2002 SP3

[Ken Johnson]

Robert in Brisbane wrote:
I have a spreadsheet with many lines of data, and each line has an image with
a hyperlink embedded.
I want to be able to do a general copy and paste of JUST the hyperlink
information, into another column, but cannot seem to work out if it can be
done.

Any help would be appreciated.
Thanks


Program version: MS Excel 2002 SP3

Hi Robert,

Try this macro...

Public Sub HyperlinkNames()
Dim Shp As Shape
Dim rngDestination As Range

Set rngDestination = Application.InputBox( _
Prompt:="Select any cell in the column " & _
"to receive the hyperlink names.", _
Title:="Destination Column", _
Default:=Selection.Address, _
Type:=8)

For Each Shp In ActiveSheet.Shapes
If Shp.Type = 13 Then
On Error Resume Next
Cells(Shp.TopLeftCell.Row, _
rngDestination.Column).Value = _
Shp.Hyperlink.Name
End If
Next Shp
End Sub

If the images are Pictures, rather than drawing objects, then for every
Picture on the sheet that has a hyperlink, the macro will put the
hyperlink's name into the cell that is on the same row as the Picture
and in the column that you have chosen.
It should work OK provided the sheet does not have more than one
Picture per row.

Ken Johnson

[Ken Johnson]

Hi Robert,

If you prefer, here is a UDF equivalent...


Public Function HyperLinkName(PictureCell As Range) As String
On Error GoTo NO_HYPERLINK_PICTURE
Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
If Shp.Type = 13 Then
If Shp.TopLeftCell.Row = PictureCell.Row Then
HyperLinkName = Shp.Hyperlink.Name
End If
End If
NO_HYPERLINK_PICTU
Next Shp

End Function

If the Pictures are all in the same column, then if the top-most
picture is in D2 then...

=HyperLinkName(D2) filled down as far as needed should work.

If the Pictures are in different columns (and there is only one Picture
per row), then if the top-most Picture is in row 2 then...

=HyperLinkName(2:2) filled down as far as needed should work.

Ken Johnson

[Robert in Brisbane]

Ken
Thank you VERY much. That did the job perfectly, and saved me quite a few
hours doing it manually.
And so easy, even for me ....

Robert

"Ken Johnson" wrote:

Hi Robert,

Try this macro...

Public Sub HyperlinkNames()
Dim Shp As Shape
Dim rngDestination As Range

Set rngDestination = Application.InputBox( _
Prompt:="Select any cell in the column " & _
"to receive the hyperlink names.", _
Title:="Destination Column", _
Default:=Selection.Address, _
Type:=8)

For Each Shp In ActiveSheet.Shapes
If Shp.Type = 13 Then
On Error Resume Next
Cells(Shp.TopLeftCell.Row, _
rngDestination.Column).Value = _
Shp.Hyperlink.Name
End If
Next Shp
End Sub

If the images are Pictures, rather than drawing objects, then for every
Picture on the sheet that has a hyperlink, the macro will put the
hyperlink's name into the cell that is on the same row as the Picture
and in the column that you have chosen.
It should work OK provided the sheet does not have more than one
Picture per row.

Ken Johnson

[Ken Johnson]

Robert in Brisbane wrote:
Ken
Thank you VERY much. That did the job perfectly, and saved me quite a few
hours doing it manually.
And so easy, even for me ....

Robert

"Ken Johnson" wrote:

Hi Robert,

Try this macro...

Public Sub HyperlinkNames()
Dim Shp As Shape
Dim rngDestination As Range

Set rngDestination = Application.InputBox( _
Prompt:="Select any cell in the column " & _
"to receive the hyperlink names.", _
Title:="Destination Column", _
Default:=Selection.Address, _
Type:=8)

For Each Shp In ActiveSheet.Shapes
If Shp.Type = 13 Then
On Error Resume Next
Cells(Shp.TopLeftCell.Row, _
rngDestination.Column).Value = _
Shp.Hyperlink.Name
End If
Next Shp
End Sub

If the images are Pictures, rather than drawing objects, then for every
Picture on the sheet that has a hyperlink, the macro will put the
hyperlink's name into the cell that is on the same row as the Picture
and in the column that you have chosen.
It should work OK provided the sheet does not have more than one
Picture per row.

Ken Johnson



Hi Robert,

You're welcome.
Thanks for the feedback.

Ken Johnson