HYPERLINKS

[CUK-2019]

Dear Excel users and/or expert(s)

I'm working with hyperlinks in Excel-2010. I've a quite big table of more than 3000 rows in my worksheet and all the cells in the most left column are connected to jpeg files (photos) staying in two different folders. This is accomplished by using static hyperlinks (created via the "insert hyperlink" tool) which were created by former users of this table.

One of the target folders containing the jpeg files is located in the same folder as my active excel file and the other is staying in another folder of our company network.

I have a specific problem which is to see the "link location" (target file and its address) of each cell with above mentioned static hyperlinks. You know, if these hyperlinks were of dynamic type, I could simply press the "show functions" button in the functions tab and get all the link locations separately or together with their "friendly names" by using a text function for all the cells. However, all I can do with static hyperlinks is going to each cell one by one then right click to select the "edit hyperlink" tab from the drop down menu, copy the link location and the friendly name from the related fields in the dialogue box of "insert hyperlink" tool and finally paste them onto empty cells. This sounds crazy in case of more than 3000 cells with static hyperlinks.

Please advise whether there is an easier way to get link locations of hyperlinks created by "insert hyperlink" tool. I believe this is possible by using visual basic codes but unfortunately I have no coding experience.

Many thanks in advance for all the replies....

Cumhur U. Kayan 

[#2]

@CUK-2019 The macro below creates a list of the selected hyperlinks 5 columns to the right of your selection (adjust the Offsets in the macro if you want to change where the results go):

Sub ExtractDataFromLinks()
    Dim C As Range
    For Each C In Selection
        With C.Hyperlinks(1)
            C.Offset(, 5).Value = .Address
            C.Offset(, 6).Value = .SubAddress
            C.Offset(, 7).Value = .Name
        End With
    Next
End Sub

[#3]

THANKS FOR A MILLION TIMES JAN :) !!!

[#4]

:) You're welcome