How to Save Chart as Image Using VBA in Excel (2 Easy Methods)

The article will show you how to save a chart as an image in Excel by using VBA. In general, you can save a chart as an image from the Context Menu by right-clicking. However, you cannot save all the charts as images if your data contains a lot of them. Fortunately, you can use VBA to apply the command of saving all the charts at once. This is really a time-saving approach. You will see the processes and advantages of this application later in this article.

Save Chart as Image Using VBA in Excel: 2 Easy Methods

In the dataset, we have a Profit statement of a shop for the first six months of a year. We will make charts following this information and also save them as images on the computer using VBA.

1. Save a Single Chart as Image

This section will show you how to save one single chart using VBA. Let’s get to the procedures below.

Steps:

First, select the data and go to Insert >> Chart >> 2-D Column >> Clustered Column Chart.

After that, you will see a corresponding Column Chart in your sheet.

Next, go to the Developer Tab and then select Visual Basic.

After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

Now, type the following code in the VBA Module.

Option Explicit
Sub SaveChart()
Dim Chart_Obj As ChartObject
Dim Image_Chart As Chart
Dim nmyFileName As String

Set Chart_Obj = Sheets("column chart").ChartObjects(1)
Set Image_Chart = Chart_Obj.Chart
nmyFileName = "Image_Chart.png"

On Error Resume Next
Kill ThisWorkbook.Path & "\" & nmyFileName
On Error GoTo 0
Image_Chart.Export Filename:=ThisWorkbook.Path & "\" & nmyFileName, Filtername:="PNG"

MsgBox "Chart Saved As Image File"

End Sub

In this code, we declared Chart_Obj As ChartObject and Image_Chart as Chart. Then we set Chart_Obj to 1 as we have only one chart in the column chart worksheet by using the ChartObjects property. After that, we set a name for the image and its file location. Also, we defined the type of the image as .png. You can set it to other types like .jpg, .pdf, etc. The image will be saved in the file location of this chart’s workbook.

Next, go back to your sheet and Run the Macro named SaveChart as it is our current Macro.

Thereafter, you will see a message box showing ‘Chart Saved As Image’. Just click OK.

Later, go to the file location of your current Excel workbook and you will find your chart image. In the code, we named our chart Image_Chart. So this image is saved with this name.

Open it and see that the file is saved as a PNG

Thus you can use Excel VBA to save a single chart as an image.

2. VBA to Save All Charts of the Workbook

If your workbook contains multiple charts, it will be tiresome to save charts as images one by one. In the following description, I’ve sown the seeds to save you from this trouble.

Steps:

In the first method, we made a column chart using our dataset. Let’s make a Pie Chart this time.

Select your data and go to Insert >> Chart >> 2-D Pie Chart.

After that, you will see a corresponding Column Chart in your sheet.

Next, follow the steps of Method 1 to open a VBA Module.
After that, type the following code in the VBA Module.

Sub SaveAllChart()
Dim Work_Sheet As Excel.Worksheet
Dim Save_Destination As String
Dim Chart_Obj As ChartObject
Dim Chart_Image As Chart
Save_Destination = ActiveWorkbook.Path & "\"
For Each Work_Sheet In ActiveWorkbook.Worksheets
Work_Sheet.Activate
For Each Chart_Obj In Work_Sheet.ChartObjects
Chart_Obj.Activate
Set Chart_Image = Chart_Obj.Chart
myFileName = Save_Destination & Work_Sheet.Name & ".png"
On Error Resume Next
Kill Save_Destination & Work_Sheet.Name & Index & ".png"
On Error GoTo 0
Chart_Image.Export Filename:=myFileName, Filtername:="PNG"
Next
Next
MsgBox "Charts Saved As Image Files"
End Sub

In this code, we declared Work_Sheet As Excel.Worksheet, Save_Destination As String, Chart_Obj As ChartOObject and Chart_Image As Chart. Then we set Save_Destination to the file location of this workbook by using the ActiveWorkbook.Path Property. Then we used a nested For Loop to identify the charts of each worksheet and name them after the names of the worksheets by using the ChartObjects and Name properties. Also, we set the type of the image file to .png. The images will be saved in the file location of these charts’ workbook.

Next, go back to your sheet and Run the Macro named SaveAllChart as it is our current Macro.

Thereafter, you will see a message box showing ‘Charts Saved As Images’. Just click OK.

Later, go to the file location of your current Excel workbook and you will find your chart image. In the code, we named our chart after their corresponding worksheet names which are column chart and pie chart.

Open them one by one. You will see the 2-D Clustered Chart if you open the column chart.

And you will see the 2-D Pie Chart after opening the pie chart

Thus you can use Excel VBA to save all the charts as images.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Download Practice Workbook

VBA to Save Chart as Image.xlsm

Conclusion

Suffice to say, we can conclude that you will learn two basic tips to save Excel charts as images by using VBA. If you have any better methods or questions or feedback regarding this article, please share them in the comment box.

Get FREE Advanced Excel Exercises with Solutions!