Written by Allen Wyatt (last updated January 23, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7484) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to add pictures to your worksheet, even within a macro. However, you might have a bit harder time ...
Discover MoreThe mouse wheel, by default, controls scrolling vertically through your worksheet. If you don't want the wheel to control ...
Discover MoreMacros are often used to process the data stored in a worksheet. Some of these processing needs can be pretty specific to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments