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

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

  • Comments 2
  • Likes
Hey, Scripting Guy! Question

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

-- GA

SpacerHey, Scripting Guy! AnswerScript Center

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?

Note. According to urban legend, Hostess Twinkies have a shelf life of anywhere from 50 to 100 years; pretty lame compared to nonfat raspberry yogurt, but pretty good for a pastry product. As a matter of fact, there are people who claim that Hostess stopped making Twinkies decades ago, and has simply been selling the old ones ever since. (The story is that Hostess terribly overestimated the number of Twinkies it would sell, and ended up making billions more than it needed.) Food experts say that, in reality, Twinkies have a shelf life of about 25 days, but there are unconfirmed reports of Twinkies surviving reasonably well for more than 30 years.

Which is something none of the Script Guys can claim to have done.

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"

Note. We really do guarantee that this column has a shelf life of at least 2,895 years. If, come June, 4902, you are unable to locate this particular column, well, email scripter@microsoft.com (in English, if possible) and we will refund your money, no questions asked.

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:

Spacer


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")

Office Excel 2007: The scripts in this article will work just fine in Excel 2007. The only difference is that the active cell doesn’t seem to impact the position of the picture. So the preceding script, where we set B10 as the active cell, will simply make B10 the active cell; the position of the picture won’t be any different than it was when the active cell was A1. If you want to specifically set the position of the picture, you can use the AddPicture method of the Shapes object:

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

The first parameter to AddPicture is (obviously) the path to the image. The second and third parameters have to do with how the picture is embedded. The final four parameters – in this case 0,0,100,200 – are the coordinates within the worksheet where the picture will appear and the size of the picture. See the Excel Developer Reference for more details.

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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Okay, ScriptGuy, I have not idea what you're talking about. I'm supposed to be able to add a background color to my worksheet without doing my own code. Are you just so geeky you just want to do it yourself or I'm assuming since you said you didn't even you know you were supposed to be able to do it that you just don't know how? Who are you anyway? But if you can help, thanks, I'd surely appreciate it.

  • Um, sorry, egg of face, I'm just trying to set a color - my bad - but still if you don't mind helping a smart-alex dork, I'd still really appreciate it. Thanks