+ Reply to Thread
Results 1 to 8 of 8

How to hyperlink photos in Excel File

  1. #1
    Registered User
    Join Date
    12-21-2006
    Posts
    46

    How to hyperlink photos in Excel File

    Dear Sirs,

    Am in need for this solution very badly and what could be a better place than excelforum !

    I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.

    In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.

    In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.

    Problem :

    I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.

    Solution Needed :

    Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.

    Thanks a million and warm regards ::: Jack
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,312

    Re: How to hyperlink photos in Excel File

    Can you not make use of the hperlink formula?

    =HYPERLINK("C:\" & ROW()-1 & ".bmp",B2)

    change path and file extension to suit.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    13,666

    Re: How to hyperlink photos in Excel File

    This can't be done with formulas. I have provide a short VBA sub to do this, which runs when you press the button at the top of the Photos column.

    You didn't give any information about your file naming convention, so I assumed that the photo files use a ".JPG" extension. If this is wrong you can modify the code to suit. (If you are unfamiliar with VBA, you can go to the Developer tab on the ribbon, click Design Mode, then double-click on the Refresh Links button. This will take you to a screen where you can see the code. See the place where it says ".JPG"? Change that to whatever you use.)

    An alternative would be to implement the Worksheet_Change function and update links whenever one is changed. That has some advantages but I got the sense this is a one-time setup and you would need to process data already in place, so I went with the button.

    P.S. I happen to be a "sir" but we're not all sirs!
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    13,666

    Re: How to hyperlink photos in Excel File

    Quote Originally Posted by Andy Pope View Post
    Can you not make use of the hperlink formula?

    =HYPERLINK("C:\" & ROW()-1 & ".bmp",B2)

    change path and file extension to suit.
    As usual, I again have learned something from Andy Pope. I was unaware of the HYPERLINK function and his solution is far easier than mine!

  5. #5
    Registered User
    Join Date
    12-21-2006
    Posts
    46

    Re: How to hyperlink photos in Excel File

    Quote Originally Posted by Andy Pope View Post
    Can you not make use of the hperlink formula?

    =HYPERLINK("C:\" & ROW()-1 & ".bmp",B2)

    change path and file extension to suit.

    Dear Andy Pope Aka Moderator Sir,
    Cannot thank you enough !!!
    Sincere Regards and Thanks a lot for your kind help.

    Jack

  6. #6
    Registered User
    Join Date
    12-21-2006
    Posts
    46

    Re: How to hyperlink photos in Excel File

    Quote Originally Posted by 6StringJazzer View Post
    This can't be done with formulas. I have provide a short VBA sub to do this, which runs when you press the button at the top of the Photos column.

    You didn't give any information about your file naming convention, so I assumed that the photo files use a ".JPG" extension. If this is wrong you can modify the code to suit. (If you are unfamiliar with VBA, you can go to the Developer tab on the ribbon, click Design Mode, then double-click on the Refresh Links button. This will take you to a screen where you can see the code. See the place where it says ".JPG"? Change that to whatever you use.)

    An alternative would be to implement the Worksheet_Change function and update links whenever one is changed. That has some advantages but I got the sense this is a one-time setup and you would need to process data already in place, so I went with the button.

    P.S. I happen to be a "sir" but we're not all sirs!

    Thank You Sir,
    Hv downloaded the file and lemme give it a try !
    Thanks again for kind help.
    Regards - Jack

  7. #7
    Registered User
    Join Date
    02-07-2017
    Location
    uae
    MS-Off Ver
    2007
    Posts
    1

    Re: How to hyperlink photos in Excel File

    Dear friends,

    Just wanted to ask, what if the photos are not on the same folder, how do you hyperlink it on the same excel.
    (referring to the same scenario that our friend Jack in his excel sheet)

    Any help from this.

    Thank you for the necessary help.

    Best Regards,
    Josh

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    13,666

    Re: How to hyperlink photos in Excel File

    Quote Originally Posted by joshualee View Post
    Dear friends,

    Just wanted to ask, what if the photos are not on the same folder, how do you hyperlink it on the same excel.
    (referring to the same scenario that our friend Jack in his excel sheet)

    Any help from this.

    Thank you for the necessary help.

    Best Regards,
    Josh
    Normally we would ask you to open your own thread but your question is directly related to the question and answer here.

    The answer is in Andy Pope's post above:
    Quote Originally Posted by Andy Pope View Post
    Can you not make use of the hperlink formula?

    =HYPERLINK("C:\" & ROW()-1 & ".bmp",B2)

    change path and file extension to suit.
    Just update that formula--change "C:\" to whatever folder you're using.
    Jeff

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.

    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會

    If someone helped you please click on the star icon at the bottom of their post
    If your problem is solved please go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1