Inserting images based on an Excel list of items

I want to create an Excel workbook that will read lists of items from another workbook and create new worksheets in our new workbook populated with images and their titles based on the contents of the other workbook.

To explain this further:

I have a workbook with several worksheets. We'll call the workbook BookA and it's sheets SheetAA, SheetAB and SheetAC for reference.

Each of these worksheets has a list of items in column A, starting at cell A15.

Each item in this list may have up to three images associated with it (though there may also be none).

All images for all sheets are stored in the same folder for a given version of BookA, and all images are of the same dimensions.

Image names are based on the item name with one of three postfixes, for example:
'item_name_001' may have images named 'item_name_001_P.jpg', 'item_name_001_C.jpg' or 'item_name_001_S.jpg'. The postfixes are always an underscore followed by 'P', 'C' or 'S' and the .jpg file extension.

For each of the desired sheets in BookA, I want to run down the item list in order and identify if each of these associated images exist in the folder. If it does, I want to import it to the new workbook in a correspondingly named new sheet.

For these new sheets, the image layout is important. The images must span 3 columns. The first image should be placed in cell A1, the second in B1 and the third in C1. Where more than one image exists for an item, they should be ordered 'P', 'C', then 'S' (from the image name postfix). If there are only one or two images for a given item, then the next item's images should immediately follow it in the row until we reach our maximum of column 3, then wrap down to the second row below.

Each image also requires the image name to be placed in the cell immediately below it.

Thus we have 2 rows of 3 columns for each row of items - a row of 3 images with a row of 3 image names underneath. That is:

Cells A1-C1 contain 3 images.
Cells A2-C2 contain 3 image titles.
Cells A3-C3 contain 3 images.
Cells A4-C4 contain 3 image titles.
... and so on until the end of a sheet's list of items.

The image dimensions are constant, but do need scaling from the original size. The ideal is for them to remain 8.5cm height while maintaining their aspect ratio (the current manually created version I'm looking at has an image size of 8.49cm x 11.32cm, though no doubt there's some rounding to that).

Currently on my manually created sheets, a row height of 240 for the images and 15 for the titles looks about right for Arial 8 point text for the titles.

I'd like to be able to select BookA's title and location, and also the source directory for the images to be used. Having done so, our new workbook should generate corresponding SheetAA, SheetBB, SheetCC populated with our images in the format above. No other information is needed on these sheets.

The explanation sounds more complex than it really is when you break it down but I wanted to be as clear and specific as possible. Sadly my VBA skills aren't quite up to this as I've not used it in years.

Note the reason for my wanting a new workbook to generate these populated sheets is to avoid putting any code into the original source document, that needs to remain 'clean'.

Thanks in advance for any assistance with this, it will be a huge timesaver for me.

I'd like to be able to select the source workbook for the list data, and also the source folder for the images, then run the script to generate the sheets full of images.

Doesn't matter too much to me how this is done, whether its via the Windows file dialogues or simply copy/pasting the paths into a cell (much easier to do that than mess around with dozens of images repeatedly on a daily basis). A file selection dialogue would be nice but far from essential.

As per my first post, ideally I want to run this code in a standalone workbook so it doesn't impact on my workbook with the list data in any way other than reading from it.

Ok, lets simplify this.

How can I search an Excel list, then search for a corresponding image in a predefined folder?

Assume images are in C:\Images\

Item list starts in Cell A15.

We'll assume item names are the same as the image names, minus the .jpg file extension.

My main issue is to check for the existence of an image and return a true/false response to act on that further. I don't know how to parse the list to add the .jpg extension then test for the existence of the image file in a folder.

That's great, thank you, a big help.

Next step: How do I then load that image and place it on a page in Excel, with a height of 8.5cm and maintaining the aspect ratio?

With these two things it should be relatively simple to put the rest together.

Well, thanks to your pointers and a little Googling, I have a working solution now.

It needs a final tidy up to set the column and row sizes that the images are placed in, but otherwise its working as intended during my brief tests.

Here's the final code I came up with:

Please Login or Register  to view this content.

I can now expand on this to search other data sheets and compile multiple image sheets. This has been a nice little learning exercise.

The code isn't too pretty or advanced, but it does what's needed of it. Any pointers to tidy things up or make it more compatible with other versions of Excel would be nice but not necessary, so don't feel obliged.

I've discoverd my code above has version issues.

It works fine on my own PC using Excel 2003, but a colleague using a later version finds it will somteimes insert a linked image instead of embedding it. That's fine while we're working on the network, but as soon as we take the files offline our images only show the link - see the attached jpeg for an example (I've blurred most of the file path for anonimity).

Can anyone suggest a fix to my pasting code that will force the image to be embedded instead of linked?

It does appear to work as long as the images in the folder are available and the workbook can access those. Move the images (or rename the folder) and you'll find that the images in the workbook will randomly appear as links instead of being embedded. There doesn't appear to be any consistency to when this will happen.

Its possible to spot the affected images - if you double click them once inserted in the spreadsheet to bring up their properties, the path will appear in the 'web' tab if it's been linked instead of embedded.

I've resolved it by changing the section of code that pastes the image to:

Please Login or Register  to view this content.

This seems to work on both the old and new versions of Excel and is much neater, though it doesn't fit the image to a cell but to a specific location on the page instead. I'm seeing a slight misalignment vertically once I get down to row 10 but its rare I need that many.