In other tutorials, we’ve described how to create charts in VBA and even how to create scatterplots with VBA. In this tutorial, we’ll show you how to export a chart as a picture using VBA. We’ll be using a line chart with quarterly sales per branch throughout this tutorial. Truth is, you could make a bar chart or line chart but since this is data over time and across four branches, a line chart is cleaner and makes visualization easier.
Exporting charts as images is useful when attaching charts as images to an email. Images are immutable, so while you can embed a chart in PowerPoint, if you want to ensure no one changes the chart, an image may be a better solution.
To practice, you can download the CSV of the sales data (all fictitious, of course).
Identify the Correct Chart
First you need to find the right chart to export. Most people create a chart directly on the worksheet that contains the data. This is technically an embedded chart, which must be wrapped by a ChartObject container to differentiate it from the underlying worksheet object. If you’re exporting charts made by a GUI-user, you’ll need to find the right ChartObject first (which contains the chart itself).
As is standard in VBA, collections of objects are the pluralized name of the object: ChartObject is the individual object, and ChartObjects is the collection.
Each Sheet has its own ChartObjects collection, so there’s a bit of layering here. Let’s say your user responsibly names their charts and sheets, so you have a sheet
Screenshot of Sheet and Chart, with the chart name in the upper left
There are a couple options for identifying this chart using VBA, but here’s one way using the Chart name, found in the upper left of the screenshot above:
Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW")Irresponsible Naming
If your user does not logically name things, it may take some guesswork to programatically determine which chart to export or you can create a dropdown list of all the chart names detected.
One thing you could do is try ActiveSheet.ChartObjects.Count, which counts the chart objects on the active sheet. With some luck, the user may want to export the sole chart on the active sheet. If that’s the case, the count is 1 and you can just export that chart.
If the count isn’t 1, you’ll probably need to iterate through the ChartObjects in the collection, store their names (hopefully not
The Export Method
Now that you have identified the chart or charts you want, you can export it as an image. But first, if you’d like to do any other chart manipulation, it would be wise to create a named object to identify your chart, like this:
Dim salesChart As ChartObject
Set salesChart = Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW")This way you can manipulate
File name (and File path)
You’ll save your chart as an image using the .Export method. Of the three parameters for .Export, only the filename is required. It’s a good idea to include the full path instead of just the name you want to give your image. Otherwise, the user may have trouble finding the exported image later.
You can either hardwire the path, or customize it at the time of execution using strategies, like these:
ThisWorkbook.Path,ActiveWorkbook.Path, or some other way to find the file path of the workbook you’re currently working on.- Use a folder picker dialog to allow your users to select their own path using a graphical interface.
- Use an
InputBoxor Application.InputBox to let your user manually copy and paste a path of their choosing.
If the chart is responsibly named, you can just use salesChart.Name for the name itself (here .Name is
You’ll also need to provide a file extension or it will be saved as a generic file, leaving less savvy users confused on how to open it. JPG, GIF, and PNG are all common filetypes.
Optional Filter Name
The Export method gives you the ability to choose a FilterName, which, according to the Microsoft documentation, identifies the “graphic filter as it appears in the registry”. These are the file extensions. VBA is smart enough to know which filter you want from the filename, though, so this is rarely necessary.
There’s a final optional parameter for showing a dialog box to choose the FilterName. Unfortunately, when I set this to TRUE, my machine doesn’t seem to care and I’ve never been able to reliably use this parameter.
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
Resizing the Chart
When Excel exports a chart as an image, it will retain the current size of your chart in pixels. You’ll need to resize the chart before exporting if if you want your images saved with certain dimensions. This is where our .Height and .Width, which you can use to programmatically resize the chart before exporting it as a picture.
I emphasize these properties are read-write because it’s bad form to resize a user’s chart, especially since they’ve likely spent a lot of time getting it just right. The best practice would be to store the original height and width to variables, resize the charts to the size you want to export them, then restore the original the chart to its original height and width.
The height and width properties take values in points, where one point is 1/72 of an inch. To ensure sufficient resolution and readability when exporting without distorting the original chart, you may want to include some code like this to capture the original dimensions before resizing the chart. That way, you can restore the original dimensions after exporting the chart image at the size you want.
'capture original dimensions
origHeight = salesChart.Height
origWidth = salesChart.Width
'resize chart
salesChart.Height = 500
salesChart.Width = 500Exporting Your Chart(s)
So far you’ve identified your chart, your path, and your filetype and you’ve determined an appropriate size for your chart. Now you’re ready to export.
Assuming you’ve stored the file path into a string, your full export macro may look like this:
Sub exportCharts()
'dimension and set objects
Dim endFileName As String
Dim salesChart As ChartObject
Dim origHeight As Integer, origWidth As Integer
Set salesChart = Sheets("Quarterly Sales per Branch").ChartObjects("Sales NCW")
'capture original dimensions
origHeight = salesChart.Height
origWidth = salesChart.Width
'resize chart
salesChart.Height = 500
salesChart.Width = 500
'build file path and name
'make sure to concatenate the backslash or you will land in the
'parent folder with the target folder in the filename
endFileName = ThisWorkbook.Path & "\" & salesChart.Name & ".jpg"
salesChart.Chart.Export endFileName
'restore original dimensions
salesChart.Height = origHeight
salesChart.Width = origWidth
End SubIf you have multiple charts on a single worksheet, iterate through all the ChartObjects to save them all at once using a loop, like this:
For Each cht In ActiveSheet.ChartObjects
endFileName = ThisWorkbook.Path & "\" & cht.Name & ".png"
cht.Chart.Export endFileName
Next chtNote: don’t forget to concatenate the backslash to the Path, otherwise your target folder will be part of the filename, not the parent folder.
Now you know how to export embedded charts as images, like jpg, png and gif. Once they’re exported, you can attach the results to emails or embed them in presentations as immutable objects. We’ve also shown you how to loop through the ChartObjects collections to help you export multiple chart images at once.
The .Export method really is a simple method. In fact, the hardest parts are building the path and identifying the actual chart you want to export. Once you master that, you’ll be rapidly exporting charts in no time.
If you found this tutorial helpful, I hope you’ll subscribe using the form below. We’ll send you tips and tutorials to make sure you’re exercising VBA to the fullest. It can do a lot of powerful things, and we’ve only just scratched the surface with this tutorial.
