Hey, Scripting Guy! How can I save an Office Excel chart as a picture?

— JB

Hey, JB. You know, yesterday was a good day for the Scripting Guy who writes this column. If the phone rings at the Scripting House and if it’s for the Scripting Guy who writes this column, well, it’s never really for the Scripting Guy who writes this column; instead, it’s someone trying to sell him something, or someone wondering if they can count on him to donate to their annual fund-raising drive. (Not being particularly smart, the Scripting Guy who writes this column always thought annual meant “once – and only once – a year.”) The same is true for the mail: if something is addressed to the Scripting Guy who writes this column (as opposed to Occupant or Current Resident) then it’s a bill.

Either that, or a restraining order from one of the other Scripting Guys.

Yesterday, however, was different: yesterday the Scripting Guy who writes this column actually got a letter addressed specifically to him. As it turns out, a new European day spa was opening in town, and they were offering the Scripting Guy who writes this column a chance to get a complete makeover, at 50% off the regular price. (Editor’s Note: Obviously they’ve never seen the Scripting Guy who writes his column, or they’d never have offered a discount on a complete makeover. They would have charged extra.)

Now, admittedly, the Scripting Guy who writes this column thought these folks were being a bit naïve: after all, if they really do think they can give him a complete makeover in just one day, well …. Still, he was intrigued by the offer. What we’re about to say might come as a shock to those of you who read this column regularly, and have understandably pictured the Scripting Guy who writes this column as very cultured and refined. But it’s true: the Scripting Guy who writes this column has never been to a European day spa. Never. “Maybe it’s time to give this a try,” he thought.

With that in mind he perused the different offerings, and immediately discovered that he was way out of his league. For example, he wasn’t totally sure about the Full Body Chocolate Raspberry Massage, a massage that promised to give him “skin like you’ve never had before.” Usually if something includes the tem Chocolate Raspberry the Scripting Guy who writes this column will take at least one. This was a little different, however. Plus there was the promise about skin like he’s never had before; what exactly did that mean? After all, the Scripting Guy who writes this column has never had skin like an iguana or a pterodactyl before.

Which isn’t to say that having skin like an iguana or pterodactyl might not be an improvement over the skin he currently has.

Even more confusing was the Heated Swiss Chocolate Milkshake Foot Bath and Beverage. Philosophically, the Scripting Guy who writes this column had difficulty figuring out how you could heat a milkshake and still have a milkshake. Far more important, he wasn’t sure whether this offer consisted of one item or two. Do you get a foot bath and then, on top of the foot bath, get a beverage as well? Or was this one item, a combination foot bath and beverage? To tell you the truth, the Scripting Guy who writes this column didn’t really want to know.

As it turned out, though, it was all a moot point: even at 50% off a day at the European day spa cost $299. For $299, the Scripting Guy who writes those column can make 100 or so trips to Dairy Queen and heat his own milkshakes.

Besides, he doesn’t have time to lounge around at a European day spa; he has work to do:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

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

objWorksheet.Cells(1,1) = "Operating System"
objWorksheet.Cells(2,1) = "Windows Server 2003"
objWorksheet.Cells(3,1) = "Windows XP"
objWorksheet.Cells(4,1) = "Windows 2000"
objWorksheet.Cells(5,1) = "Windows NT 4.0"
objWorksheet.Cells(6,1) = "Other"

objWorksheet.Cells(1,2) = "Number of Computers"
objWorksheet.Cells(2,2) = 145
objWorksheet.Cells(3,2) = 987
objWorksheet.Cells(4,2) = 611
objWorksheet.Cells(5,2) = 41
objWorksheet.Cells(6,2) = 56

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.Export "C:\Scripts\Test.jpg", "JPG"

Well, OK: not too much work. We’re actually not going to explain the details behind creating a chart in Microsoft Excel; that’s because we have entire series of articles devoted to that topic. Instead, we’ll just briefly note that our script starts out by creating an instance of the Excel.Application object and then sets the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We use the Add method to add a new, blank workbook to our running instance of Excel, then use this line of code to bind to the first worksheet in that workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

That’s all pretty easy. What follows next are several lines of code designed to add data to the worksheet. When that’s done, we select all the data (using the UsedRange property and the Select method), then use these two lines of code to create a chart:

Set colCharts = objExcel.Charts
colCharts.Add()

The resulting chart will look a little like this:

And no, sorry, it’s just a plain old chart. We haven’t yet figured out how to make a combination chart and foot bath, although we plan to continue the research.

Now that we have a chart it’s time to export said chart as a picture. In order to do that we need to create an object reference to the chart itself. That’s something we can do by binding to the chart (which, of course, is the first – and only – chart in our Charts collection) and then using the Activate method to make this the active chart:

Set objChart = colCharts(1)
objChart.Activate

At that point, saving the chart as a picture is as easy as calling the Export method:

objChart.Export "C:\Scripts\Test.jpg", "JPG"

As you can see, all we do here is call the Export method, handing that method two parameters: the complete path to the resulting picture file, and the type of picture file we’re creating. In this example, we’re creating a file named C:\Scripts\Test.jpg and we want the resulting picture to be a .JPG file; hence the “JPG” parameter. Can we use other graphic types? You bet; for example, we know for sure that charts can be saved as .GIF files or as .PNG files. Can charts be saved in other formats? Probably, but we didn’t try any other format. We were saving that to help pass the time while getting our Chocolate Raspberry massage.

Of course, we know the question that’s now on everyone’s mind: will the Scripting Guys be giving away Heated Swiss Chocolate Milkshake Foot Baths and Beverages as part of the 2007 Winter Scripting Games? Just a second; we’ll check on that ….

No, sorry; we won’t be. However, we are giving away 250 Dr. Scripto Bobblehead dolls. On top of that, the good folks at Manning Publications are giving away 20 copies of Bruce Payette’s new book Windows PowerShell in Action. Is there a better and more definitive book on Windows PowerShell? Well, if there is, we haven’t seen it. This is a prize worth winning.

And how can you win one of these great prizes? Well, the requirements are pretty steep: you have to enter at least one event in the Scripting Games. That’s it. Enter an event (you don’t even have to successfully complete the event!) and you’ll automatically be entered in a random drawing for one of these great prizes. We’re sorry that we can’t give Heated Swiss Chocolate Milkshake Foot Baths and Beverages. But we think the bobbleheads and Windows PowerShell books make for pretty good consolation prizes.