Sunday, October 14, 2007

Pulling Images into Excel

The other day I did a quick $50 Project for a guy who asked if there was a way to automatically pull images into a spreadsheet based upon a values in a range of know, like for an inventory list or something.


Actually, the VBA code involved is quite simple -- one line really. Let's say that the value in the cells is actually the name of the image (only without the file type extension) that you want to pull in. That code might look like this:

ActiveSheet.Pictures.Insert(<IMAGE_DIRECTORY> & "\" & cel.Value & ".jpg").Select

Pretty simple. Now, one tip that I'd pass along when working with images or other similar objects via VBA in Excel is to immediately rename the object, since it can become cumbersome trying to figure out if it's called shape1 or shape2 or shape 300, etc. So I would rename the image to that same value in the cell, so it's easy to reselect later if you need to:

Selection.Name = cel.Value

It does get a bit more involved, though, as you probably want the images to fit nicely in a certain column and/or row. To do this you first have to determine what the height and/or width is (and adjust it if you wish) and then determine what the dimensions of the image you've imported are and adjust them to fit. Also, you probably would want to check to see if an image already exists and delete and replace it.

The code to do these things is pretty straightforward. If you'd like to learn how to do these sort of things, I'd encourage you to record these sort of actions using the macro recorder in Excel and then look at the code and see how it works. Actually, that's the way I'd suggest you learn pretty much anything VBA in Excel. That's what I do...

I added some of this other functionality to the file, which, Excel_Geek Insiders, is on it's way.




Jonah said...

Excel 2007 has trouble doing this when the source of the image is on the web. Works fine in 2003 though.

Artemy Dufondi said...

An add-in called Picture Manager For Excel simplifies image integration to Excel versions 2003 thru 2013. For more information about the features and free demo download visit