Making a Photo Catalog from a Folder of Photos in Excel using Macros

Making a Photo Catalog from a Folder of Photos 

IAN is making a list of all his advanced photographs in Excel. All the photographs (around 5000 of them) are in a solitary organizer. He might want to embed the photographs to one side of the photograph's portrayal, then, at that point put a hyperlink to all the photographs to grow the thumbnail to a bigger photograph. The present moment IAN is doing this individually and it is making him insane, so he is searching for approaches to speed the cycle up. 

Fortunately you don't need to go off the deep end very as quick; Excel gives macros that can make the work quicker and simpler. Prior to hopping into that conversation, notwithstanding, you might need to take some real time to contemplate before you approach putting all your photographs into an Excel exercise manual. 

At the point when you embed a photograph into Excel, the record size of your exercise manual is expanded by at any rate the document size of the photograph being embedded. Subsequently, if your normal photograph is 1 MB in size (minuscule with the present cameras) and you embed 5000 such photographs, then, at that point you end up with an exercise manual that has at any rate 5 GB of photographs in it. That is an enormous exercise manual, and Excel may struggle working with that much data. (How hard of a period relies upon your adaptation of Excel, how much memory is in your framework, how quick your processor is, and so on) 

You may feel that the arrangement is proportional the pictures as you place them in your worksheet, so they are more modest. While rescaling a picture causes it to seem more modest (it glances more modest in the worksheet), it's anything but truly more modest. The full-size picture is still in that general area in Excel. Along these lines, you don't lessen your exercise manual's document size at all by scaling the photographs. 

The manner in which you can lessen the record size is proportional the photographs outside of Excel, utilizing photograph altering programming, before they are embedded into Excel. All in all, you would have to stack each of the photographs into the photograph altering programming, resize the photographs to whatever thumbnail size you need, and afterward save the resized photograph into another thumbnail picture record. (You by and large wouldn't have any desire to save the resized picture over the highest point of your unique photograph.) You could then embed every thumbnail into your Excel worksheet and your resultant exercise manual document size would be more modest, albeit still straightforwardly identified with the total size of the thumbnail photographs you add to the worksheet. 

On the off chance that you actually need to embed all the photographs into your worksheet, you can do so utilizing a large scale. The accompanying model, PhotoCatalog, can search for all the thumbnail photographs and supplement them into the worksheet, alongside a hyperlink to the full photograph. It expects four things: (1) your photographs and thumbnails are all JPG pictures, (2) the photographs are in the index c:\Photos\, (3) the thumbnails are in the catalog c:\Photos\Thumbnails\, and (4) the thumbnails have a similar record names as the full-size photographs.

.

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.