How Can I Add a Background Picture to an Excel Worksheet?

ScriptingGuy1

Hey, Scripting Guy! How can I add a background picture to an Excel worksheet?

— GA

Hey, GA. You know, being the health-conscious type, each morning the Scripting Guy who writes this column eats some non-fat raspberry yogurt … right before he eats his daily doughnut. (But don’t worry: it’s a medically-proven fact that eating yogurt counteracts any potentially harmful or fattening effects of a doughnut.) This morning, as he was taking the lid off the yogurt container, he noticed the words Sell By, followed by this:

06-4902

Now, to be honest, the Scripting Guy who writes this column isn’t exactly sure how to interpret this; however, it sure looks as though the yogurt maker is suggesting that this product must be sold no later June, 4902. If that’s the case, then this Scripting Guy only has 2,895 years in which to finish off all the yogurt in his refrigerator. Needless to say, he’s beginning to regret having bought a 3,000-year supply of nonfat raspberry yogurt. What was he thinking?

At any rate, we can’t be sure if yogurt really does have a shelf life of 2,895 years; most likely it doesn’t. However, we are sure that today’s Hey, Scripting Guy! column has a shelf life of at least, 2,895 years, especially when you consider that it deals with a subject like adding a background picture to an Excel worksheet:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.SetBackgroundPicture “C:\Scripts\Test.jpg”

We have to admit that we found this to be an interesting little exercise; to be honest, we didn’t even know you could add a background picture to an Excel worksheet. (We knew you could add background pictures to a graph, but didn’t know you could do that to a worksheet.) As you can see, however, not only can you add a background picture to a worksheet, but you can do so with just a few lines of code. And that’s good: after all, we only have 2,895 years to eat our yogurt and to add a background picture to a worksheet. At the typical Scripting Guys’ pace, that’s not all that much time.

As for the script itself, we start out by creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. We then use the following two lines of code to add a new workbook to our instance of Excel and bind to the first worksheet in that workbook:

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

From there, setting a background picture is as easy as calling the SetBackgroundPicture method, passing the path to the image file as the sole method parameter:

objWorksheet.SetBackgroundPicture “C:\Scripts\Test.jpg”

Is that really all we need to do? You bet it is:

Remember, this merely sets the picture in question as the background picture for the worksheet. If you just want to insert a picture into a worksheet (without making it the background), then you need to use code similar to this:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Pictures.Insert(“C:\Scripts\Test.jpg”)

What we’re doing here is referencing the Pictures collection and calling the Insert method; that adds the image file C:\Scripts\Test.jpg to the worksheet. By default, this positions the picture so that the graphic’s upper left-hand corner is in cell A1. Suppose you’d rather have the upper left-hand corner of the picture in cell B10. That’s fine; the following script makes cell B10 the active cell (objExcel.Range(“B10”).Activate) and then inserts the picture:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Range(“B10”).Activate
objWorksheet.Pictures.Insert(“C:\Scripts\Test.jpg”)

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Shapes.AddPicture “C:\Scripts\Sunset.jpg”,1,0,0,0,100,200

That should do it, GA; let us know if you have any questions. Oh, and be sure to tune in again on June 1, 4092; on that day we’ll let everyone know whether or not nonfat raspberry yogurt really does have a shelf life of 2,895 years.

For now, however, we’ve got a doughnut to eat. How long is the shelf life of a doughnut? Let’s put it this way: if the Scripting Guy who writes this column is around, no doughnut is going to last more than a few minutes, max.

ScriptingGuy1

Follow