You might want to save your charts as images that can then be used in other applications, like email or displayed on a website.
Selecting a single image then copying/pasting or saving it, isn't too much bother. But if you want to do that for several images it quickly becomes tiresome.
I've written some VBA, and created a userform, that allows you to save any or all charts in your workbook as images.
When the userform is launched you'll see a list of all charts, in all worksheets, of the active workbook.
You can then click on whatever charts you like, and by clicking another button, all those charts are saved as images.
The Userform
I had previously written some code to export a single chart but I thought it would be much better to allow the user to see all the charts in a workbook and select the ones they wanted to save as images.
So I started desiging a form that ended up looking like this
When the form is displayed, the code behind it goes through all sheets and lists any charts it finds, showing you the sheet name, and the chart name.
By default the images will be saved to the same folder the workbook is in, but you can change this by clicking on the Browse button and choosing your own folder.
I've allowed the user to choose to save either as a PNG or a JPG, with PNG being the default. I pretty much always use PNG as the file size tends to be smaller than JPG.
To choose the chart(s) to save, just click on the chart name with your mouse. You can choose multiple charts using CTRL+Click or SHIFT+Click
With charts selected, click on Export to save them.
Useful Chart Names
You can see from the image above that Excel gives charts generic names when it creates them.
You also have the issue that you can have charts with the same name, on different sheets.
If you try to save two charts with the same name, you may overwrite one of them.
To make these names unique, and more descriptive, select the chart, then in the Name Box, type in your new name and hit Enter.
When you now display the form, the chart has its new name.
Download the Workbook
This sample workbook contains all the code and the userform to save your charts as images.
Enter your email address below to download a workbook containing this VBA code.
Hi Phillip,
Awesome!
It would be great to take this a step further. Let’s say you have a dashboard with charts and tables. Once you have sliced and diced, and charts are as per requirements, you now want to create a PowerPoint presentation. It would be useful to have PowerPoint open with a new presentation and all charts/objects pasted at the click of a button.
Any idea if this is possible and the steps required?
Hi Tracy,
Yes, anything is possible.
Using vba, we can check if there is a powerpoint document open, it can create one if needed.
All depends on what you want to do: excel can look into a document to identify the pages where a specific image should go, and add the images there. A mapping will be needed, to associate excel objects with power point objects (usually, the power point document destination shape should have a tag added in Alt Text to allow programmatic identification of the shape).
Cheers,
Catalin
Excellent!
I love the Save Chart As Image!
I hope to expand it to create a basic PowerPoint application. Once complete (assuming I get it working) I’ll share and you can everyone how it should have been done.
take care
Thx Josef
Hi There, I am getting a dll error when I try to access the VBA editor. Could you help?
Hi Niall,
Yes, if you can start a topic on the forum and supply details like the exact error message – a screenshot would be great – plus your workbook.
Regards
Phil
Am gaining a lot from you. Keep it up!!!
Thanks Dickson, we are happy to hear that!
Cheers,
Catalin
Hi Philip
A very handy utility.
From my understanding (I could be wrong) Excel cannot save any images (except charts) in a workbook as a file.These images need to be “pasted” into a chart before it can be saved as a file.
If you can merge these two functions together into your utility it will be great.
Sunny
Hi Sunny,
When you insert a image, not necessarily in a chart, but in any sheet, the image is saved into the excel archive. Using vba, the excel archive can be opened with shell – namespace, and that xl/images folder from excel archive can be accessed and manipulated.
Don’t see why the image should be in the workbook before pasting in a chart, the image can be accessed more easily from any folder.
Hi Catalin
That is very interesting.
I will go explore on this option.
Thanks a million.
Sunny
Hi Catalin
I tried 2 methods to extract the images from the file:
Method 1 : Copy and rename the file as .zip
Method 2 : Save the file as a web page
Method 1 no problem.
Method 2 there are 2 copies of each image created.Just curious to know why this is so.
Hi Sunny,
To open the excel archive you don’t need to copy the file or change the extension, just a simple right click-open with-(7zip, winrar, or windows explorer). Just make sure that the check box for “use this program as default application for this file type” is not checked.
A web page needs different image sizes: thumbnails, small images, large images, to display an image in different contexts, such as preview, product pages. On mouse over action, some pages will automatically load the larger image, acting like a zoom. I guess you’ll have to check the sizes, they should not be the same.
Catalin