People use Excel to store various types of data. In many cases, this data needs an associated image. You need to know how to create a catalog of images in Excel that can be linked to our database.
HOW TO CREATE IMAGE CATALOGUES IN EXCEL
There are several methods to create an image catalog in Excel. Some Excel users prefer to implement the catalog by embedding the image associated with each record into the Excel sheet. However, if you have a database with many records you will end up with an Excel book that is too large because of the size of the images which will make it difficult to manage.
I always recommend using a VBA method because it allows us to leave the image files in a local folder on the computer and only load the images as they are used within our Excel database.
THE KEY TO CREATE THE IMAGE CATALOG
The key to having a catalog of images in Excel is to have a sheet that relates the “primary key” of our records with the corresponding image. If you are not familiar with the term “primary key” I recommend you read our previous articles on Database Design.
In the following example, you will see that I have a list of 5 Blu-ray movies and each of them has a unique product key.
How to create a catalogue of images in Excel
The images of each product will be stored in a directory called “images” and will have the same name as the product code to which they belong. Having the images with the same name as the product code will make it easier to manipulate them.
Repository of images for catalogue in Excel
INSERT AN IMAGE CONTROL
In order to be able to observe the product face image, I will insert an Image Control which is a type of ActiveX control. To do this I must go to the Programmer tab and in the Insert button select the appropriate control.
CREATE THE MACRO TO SHOW THE IMAGE
For this example, I will show the image when selecting a cell in the Code column. I will use the event Worksheet_SelectionChange. Right click on the name of the sheet and select the option View code and in the Visual Basic Editor select the event SelectionChange. Next, you will see the VBA code for that event:
Macro for Excel image catalog
The important part of this code is that when we select any cell within the A2:A6 range the Picture property of the Image1 object will be modified. The LoadPicture instruction is responsible for loading the image stored on our local hard drive:
LoadPicture(ActiveWorkbook.Path & “Images\” & Target & “.jpg”)
The ActiveWorkbook.Path instruction returns the current directory of the Excel file and concatenates it with the “images” directory where our files are located. Subsequently, the Target variable is concatenated, which contains the value of the selected cell and the extension of the image files is added, which for our example are JPG files.
TESTING THE CATALOG OF IMAGES IN EXCEL
Finally, I will prove that the newly created macro works correctly for our image catalog in Excel:
Example of an Excel image catalog
There are variants on this method and surely in another article, we will have the opportunity to explore different methods to show the images associated with a record of our database in Excel. To continue with the tests you can download the workbook used along with the example images.