Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Creating a Photo Catalog from a Folder of Photos

Glenn is making a catalog of all his digital photos in Excel. All the photos (about 5000 of them) are in a single folder. He would like to insert the photos to the right of the photo's description, then put a hyperlink to all the photos to enlarge the thumbnail to a larger photo. Right now Glenn is doing this one by one and it is driving him crazy, so he is looking for ways to speed the process up.

The good news is that you don't have to go crazy quite as fast; Excel provides macros that can make the job faster and easier. Before jumping into that discussion, however, you may want to think long and hard before you go about putting all your photos into an Excel workbook.

When you insert a photo into Excel, the file size of your workbook is increased by at least the file size of the photo being inserted. Thus, if your average photo is 1 MB in size (quite small with today's cameras) and you insert 5000 such photos, then you end up with a workbook that has at least 5 GB of photos in it. That is a huge workbook, and Excel might have a hard time working with that much info. (How hard of a time depends on your version of Excel, how much memory is in your system, how fast your processor is, etc.)

You might think that the solution is to scale the images as you place them in your worksheet, so that they are smaller. While rescaling an image makes it appear smaller (it looks smaller in the worksheet), it isn't really smaller. The full-size image is still right there in Excel. So, you don't reduce your workbook's file size at all by scaling the photos.

The way you can reduce the file size is to scale the photos outside of Excel, using photo editing software, before they are inserted into Excel. In other words, you would need to load each of the photos into the photo editing software, resize the photos to whatever thumbnail size you want, and then save the resized photo into a new thumbnail image file. (You generally wouldn't want to save the resized image over the top of your original photo.) You could then insert each thumbnail into your Excel worksheet and your resultant workbook file size would be smaller, although still directly related to the aggregate size of the thumbnail photos you add to the worksheet.

If you still want to insert all the photos into your worksheet, you can do so using a macro. The following example, PhotoCatalog, can look for all the thumbnail photos and insert them into the worksheet, along with a hyperlink to the full photo. It assumes four things: (1) your photos and thumbnails are all JPG images, (2) the photos are in the directory c:\Photos\, (3) the thumbnails are in the directory c:\Photos\Thumbnails\, and (4) the thumbnails have the same file names as the full-size photos.

Sub PhotoCatalog()
    Dim i As Double
    Dim xPhoto As String
    Dim sLocT As String
    Dim sLocP As String
    Dim sPattern As String

    sLocT = "c:\Photos\Thumbnails\"
    sLocP = "c:\Photos\"
    sPattern = sLocT & "*.jpg"

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Thumbnail"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Hyperlink"
    Range("A1:C1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With

    i = 1
    On Error GoTo 0
    xPhoto = Dir(sPattern, vbNormal)
    Do While xPhoto <> ""
        i = i + 1
        Range("B" & i).Select
        ActiveSheet.Pictures.Insert(sLocT & xPhoto).Select
        With Selection.ShapeRange
            .LockAspectRatio = msoTrue
            .Height = 54#
            .PictureFormat.Brightness = 0.5
            .PictureFormat.Contrast = 0.5
            .PictureFormat.ColorType = msoPictureAutomatic
        End With
        Range("C" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
          Address:= sLocP & xPhoto, TextToDisplay:=xPhoto
        xPhoto = Dir
    Loop

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

It can take quite a while for this macro to run, depending on the type of system you are using and how many photos you are cataloging.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7484) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Related Tips:

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Stefan Manojlovic    27 Oct 2015, 07:49
Hello

Am trying to run the macro, but with 2 tweaks:
1. Each picture is inserted into its own cell, eg A1,B1,C1,D1 and so forth
2. The code appends the picture's name to column B1, B2, B3 and so forth

Thank you.
Luis    14 Oct 2015, 00:50
Hi How can i make this macro work for pictures located in folders and subfolders at a given location? Can You show me? Regards.
Luís Carlos    29 Jul 2015, 09:32
Hi Allen,
This macro helped me a lot, I really loved it.
I made two changes, in my spreadsheet all the pics where in the same position, so I need to move it manually. The second change was that I didn't have thumbs, so I used the sLocT for the both tasks.

Sub PhotoCatalog()
    Dim i As Double
    Dim xPhoto As String
    Dim sLocT As String
    Dim sPattern As String

    sLocT = "C:\Report\"
    sPattern = sLocT & "*.jpg"

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Thumbnail"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Hyperlink"
    Range("A1:C1").Select
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With

    i = 1
    'On Error GoTo 0
    xPhoto = Dir(sPattern, vbNormal)
    Do While xPhoto <> ""
        i = i + 1
        Range("B" & i).Select
        ActiveSheet.Pictures.Insert(sLocT & xPhoto).Select
        With Selection.ShapeRange
            .LockAspectRatio = msoTrue
            .Height = 54#
            .PictureFormat.Brightness = 0.5
            .PictureFormat.Contrast = 0.5
            .PictureFormat.ColorType = msoPictureAutomatic
        End With
        Selection.Cut
        Range("B" & i).Select
        ActiveSheet.Paste
        Range("C" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
          Address:=sLocT & xPhoto, TextToDisplay:=xPhoto
        xPhoto = Dir
    Loop

    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub



Thanks a lot and congratulations for macro!
Tomas Svoboda    30 May 2015, 07:08
It waorks with Excel 2013. Thanks !
anthony dipaolo    14 Apr 2015, 11:55
Have excel spreadsheet and pictures in word. The information in excel consist of names, pictures, address, email, lot number. How is this data all set into a single excel spreadsheet that once printed comes out with picture and data.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.