Friday, October 26, 2007

ExcelGeek.com blog updates look!

Did you notice? If you didn't, you should get your eyes checked or drink more Redbull.

I stayed up late the other night and completely redesigned the blog! Well, actually, the reason I did it was to have one of the new(er), fancy XML Blogger templates, as opposed to the old(er) HTML templates, so I could have access to a host of better administrative features.

I started with "Rounders 2" (by Douglas Bowman, www.stopdesign.com), but significantly modified it, changing colors to be more reminiscent of the old look (see here).

I uncluttered it significantly, added in a fancy label cloud (thanks to phydeaux3), and even made it so that viewers can hide the Meebome Chat window if they're moderately (or more) annoyed by it (thanks to Craiggers for withstanding my numerous idiotic javascript questions).

Anywho...hope you like it.

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 cells...you know, like for an inventory list or something.

Sure.

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.

Later,

Excel_Geek