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

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!

Originally Posted by Andy Pope

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!

Originally Posted by Andy Pope

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

Originally Posted by 6StringJazzer

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

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