How do I extract and save hyperlinks from images in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Robert said:
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
Click to expand...

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,

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_PICTURE:
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
 
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
 
Robert said:
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
Click to expand...

Hi Robert,

You're welcome.
Thanks for the feedback.

Ken Johnson
 
Hello,

I have the same problem that Robert had, however, neither the macro nor the function worked for me. I ma using Excel 2003 SP3. I have attached a short sample file (zip'd Excel file) in the hopes that someone will still see this new post to an old topic and be willing to help.

Thanks
 

Attachments

Back
Top