Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract and save hyperlinks from images in excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract and save hyperlinks from images in excel
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract and save hyperlinks from images in excel
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract and save hyperlinks from images in excel
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I extract and save hyperlinks from images in excel
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract photo from excel spreadsheet ? | New Users to Excel | |||
How do I extract a picture from Excel and save it? | Excel Discussion (Misc queries) | |||
Extract MS Excel Data embedded in MS Word | Excel Discussion (Misc queries) | |||
How to extract a picture from an Excel worksheet into a picture fi | Excel Discussion (Misc queries) | |||
Extract Pixel Values to Excel? | Excel Worksheet Functions |