Hyperlinking photos

[CRB1149]

I have approx. 5,000 photos in Windows Photo Viewer that I need to hyperlink into an Excel (2010) spreadsheet set up with numerous subsheets. I can do this one at a time but that will be tedious and prone to errors. How can I hyperlink larger goups (largest is 647) of photos at a time? Alternatively, if this is not possible, how might I maintain an open Hyperlink window and revert back to the last linked photo file without having to open a new window for each linkage and scroll down to the next file to be linked?
Thanks.

[daffodil11]

Are the photos named in a way you could easily reference with cells?

You can use cell("filename") and concatenation with "&", in conjunction with the hyperlink() function to create them without using that clunky interface.

[CRB1149]

Thanks for the reply, daffodil 11. I'm not sure what you're asking regarding "easily reference with cells." All photos have different file names. The file names are not in Excel at this point ... I have used the hyperlinkage to insert the file name. I have not found a way to copy file names from Windows Photo Viewer to the spreadsheet. I also don't know what the concatenation funtion is or what it does.

[daffodil11]

Concatenation merely refers to grouping things and using the & symbol is one of them.
I find it especially handy for systematically created a large number of formulas from a given set of values.



You can create many hyperlinks at once this way:

In B2, put the filepath to the folder. I have C:\Users\daffodil11\Desktop\

Select C2, run the maco below. It will create a list of files in the folder you select. In this case, my only file is daffodil.jpg

In D2, put ="=hyperlink("""&B2&C2&""","&""""&C2&""")"

Click the cell, copy, in cell E 2 right click, paste values (the 123 icon).

Now the cell actually contains =hyperlink("C:\Users\daffodil11\Desktop\daffodil.jpg",daffodil.jpg) instead of the references.

Enter the cell using F2. Hit Enter to activate. This will convert the cell into a working hyperlink, which only displays the file name.



It may not seem like a shortcut at first, but:

• You could enter the filepath to that folder from B2:B5000
• Then run the macro on C2 for all 5000 files at once
• Then copy the formula from D2 all the way down to D5000
• Then select all 5000 cells, copy, paste values into E2:E5000

And then activate. Now you've hyperlinked 5000 files in just a minute or two.






If you are unfamiliar with VB, you need to hit Alt+F11, choose Insert at the top, click Module.

Then paste the code below into the white sheet that pops up. Close that window.

To run the code, first highlight the cell you want to populate on, it Alt+F8, choose the GetFileNames macro, and hit okay.

Note: There is no Undo for things macros do, so always save first.

Please Login or Register  to view this content.

Here's an example using files in Microsoft Office
hyperlink mass production.xlsm

[CRB1149]

I believe this will work and, if it does, I'll owe you big time. Many thanks. One other question ... any way to disable that unsafe hyperlink warning?

[FDibbins]

I use a similar process for a bunch of pics I need to have hyperlinks to, and use this...

=IF(B139="","",HYPERLINK("S:\$ folder-name Photos\"&B139&".jpg",B139))

[CRB1149]

If this: =IF(B139="","",HYPERLINK("S:\$ folder-name Photos\"&B139&".jpg",B139)), is how to eliminate the unsafe hyperlink warning window, how do I apply it?

[FDibbins]

I run a macro to extract the file names from the folder 1st (I have it at work though, not at home, sorry), then use that formula based on the resulting file listing. I never get any hyperlink warning

[daffodil11]

Instead of concatenating a column of filepaths to a file name, Master Dibbins actually writes the filepath into the formula.

I tested it out and it didn't prompt me concerning hyperlink safety.

I added an updated example, demonstrating how to adapt it.

fdibbins is the hyperlink master.xlsm