Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Saving Charts and Graphs

According to poets and romantics, true art should be transitory and ephemeral: it should exist only for the moment and then disappear, never to be seen again. That’s a lovely sentiment; on the other hand, it’s probably not the sort of thing you should tell your boss when he or she asks to see those charts and graphs you’ve been working on. (“You know, boss, true charts and graphs should be transitory and ephemeral…”) We’re not saying that the charts and graphs you’ve been whipping up aren’t true art; we’re just saying it might not be a bad idea to keep a copy of those things, at least for a little while.

With that as an introduction you’ve probably already guessed the topic for today’s column: deleting appointments in Microsoft Outlook. No, wait, we’re getting ahead of ourselves; today we’re going to talk about saving your charts and graphs. In fact, in this column we’ll show you a couple different ways to take your cool-looking charts and save them, the better for future generations (and bosses) to gaze upon and admire.

If you want to save both the chart and the accompanying worksheet (that is, the worksheet containing the data used to create the chart) all you need to do is call the SaveAs method. The SaveAs method can actually be quite complicated: it includes options for password-protecting the worksheet, for saving in different file formats, and for automatically creating a backup file. We aren’t going to worry about any of those options today. That’s because all we want for now is a script that does two things. First, our script needs to create a simple little chart like this:

Second, it needs to save our new chart (and worksheet) as C:\Scripts\Test.xls. That’s all we’re asking.

And by an amazing stroke of luck, we just happen to have a script that can do both of those things:

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

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

objWorksheet.Cells(1,1) = "Africa"
objWorksheet.Cells(2,1) = "Asia"
objWorksheet.Cells(3,1) = "Europe"
objWorksheet.Cells(4,1) = "North America"
objWorksheet.Cells(5,1) = "South America"

objWorksheet.Cells(1,2) = 33
objWorksheet.Cells(2,2) = 54
objWorksheet.Cells(3,2) = 23
objWorksheet.Cells(4,2) = 41
objWorksheet.Cells(5,2) = 56

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = -4100

objChart.SaveAs "c:\scripts\test.xls"

Like we said, we won’t go over all the code in detail; suffice to say that we create an instance of the Excel.Application object and then add a new workbook and a new worksheet. We enter some data onto Sheet 1 of that worksheet, select the data, then create a chart out of it. After that all we do is activate the chart, and then use this line of code to turn it into a gee-whiz three-dimensional column chart:

objChart.ChartType = -4100

All very exciting. For today’s column, however, the only thing we really care about is the very last line in the script, the line that actually saves our new workbook:

objChart.SaveAs "c:\scripts\test.xls"

As you can see, calling the SaveAs method doesn’t have to be complicated; it can be as easy as, well, calling the SaveAs method and passing a single parameter: the path to the new workbook. That’s it; execute that line of code, and your chart (and the rest of the workbook) will be saved.

Um, probably. Your script will be saved provided that the file C:\Scripts\Test.xls doesn’t exist. If the file does exist, the script won’t automatically overwrite it; instead, you’ll have to deal with this message box:

Yes, we know: you don’t want to deal with that message box. Relax; as usual, the Scripting Guys have the answer. (Granted, we don’t always have the right answer, but….) If you’d prefer to automatically overwrite an existing file just add this line of code before you call the SaveAs method:

objExcel.DisplayAlerts = False

When DisplayAlerts is set to False, error messages and message boxes are suppressed and the script automatically goes with the default option. When you get the message box regarding an existing file, the default option is to overwrite the file. By setting DisplayAlerts to False the script will automatically overwrite the file, without displaying the message box.

Incidentally, the SaveAs method (and the deal with DisplayAlerts) works regardless of whether or not your workbook contains a chart. In other words, if you want to save a spreadsheet you just created then all you have to do is follow the same simple steps outlined here.

Of course, there might be times when you want to save just the graph, without the accompanying worksheet. This can be done by exporting the graph as a picture file; in that case you get a .GIF or .JPG image (ready to pop into a report or Web page) and no worksheet. Best of all, to export the graph all you have to do is call the Export method:

objChart.Export "c:\scripts\test.gif", "GIF"

This time we pass two parameters: the path to our new picture file and the name of the graphic filter. To export a file in JPEG format use this code:

objChart.Export "c:\scripts\test.jpg", "JPG"

We suppose it could be easier, but it’s difficult to see how.

But wait: there’s more. You can also copy your graph to the Clipboard, making it a snap to paste the picture into another application. That sounds way more complicated, but even copying a chart to the Clipboard requires just one line of code:

objChart.CopyPicture()

OK, we admit it: this method is so easy it’s almost embarrassing.

If you’re wondering why you’d ever want to copy a graph to the Clipboard, well, here’s one scenario. The following script creates a graph, copies that graph to the Clipboard as a picture, starts up Microsoft Word, and then pastes that picture into the Word document. (What do you mean, “Is that all it does?”) The code looks like this:

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

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

objWorksheet.Cells(1,1) = "Africa"
objWorksheet.Cells(2,1) = "Asia"
objWorksheet.Cells(3,1) = "Europe"
objWorksheet.Cells(4,1) = "North America"
objWorksheet.Cells(5,1) = "South America"

objWorksheet.Cells(1,2) = 33
objWorksheet.Cells(2,2) = 54
objWorksheet.Cells(3,2) = 23
objWorksheet.Cells(4,2) = 41
objWorksheet.Cells(5,2) = 56

Set objRange = objWorksheet.UsedRange
objRange.Select

Set colCharts = objExcel.Charts
colCharts.Add()

Set objChart = colCharts(1)
objChart.Activate

objChart.ChartType = -4100

objChart.CopyPicture()

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
objSelection.Paste

Granted, this might be neither transitory nor ephemeral. But it’s still kind of cool:

And, once again, it requires very little programming effort on your part. After copying the graph to the Clipboard we simply start Word, set the Visible property to True (just so we can see everything onscreen), and then use the Add method to create a new, blank document. We create an instance of the Word Selection object, then call the Paste method to paste the contents of the Clipboard (which just happens to be our graph) into the document:

objSelection.Paste

The British writer D. H. Lawrence once said, “The human soul needs actual beauty more than bread.” In other words, now that you have a script that creates – and preserves – these works of art, well, what more could you possibly need?

Speaking of which, now that you have actual beauty and thus no need for bread, any chance we could have the rest of that sandwich…?