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.
- First, select the data and go to Insert >> Chart >> 2-D Column >> Clustered Column Chart.
- 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.
Read More: How to Save Image from Excel as JPG
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.
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.
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
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.