Hello Excellers, and welcome back to another blog post in my #MacroMonday Excel tip series. Today I will show you how to write an Excel VBA macro that will save your active Excel chart as an image. This macro is handy if you regularly have to export Excel charts from your Excel workbook. Then, Excel exports the image and saves it in the exact location as your Excel workbook with one click. Or, you can specify another location.
Starting the Macro.
When you decide to write an Excel macro, you have two places to save it.
- Store it in the current Excel workbook you are working on creating.
- Save the Macro in your Personal Macro workbook.
So, what is the difference? It is simple. If the code you are writing is specific to that workbook, then store it within the workbook. If you can reuse a type of Macro, keep it in your Personal Macro Workbook. In this example, I could reuse this Macro repeatedly to convert merged cells to centre across selection. I, therefore save it in my Personal Macro workbook.
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
At this stage go ahead and insert a new module. First open the Visual Basic Editor.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
- Insert Module.
Copy The Code. How To Save An Excel Chart As An Image.
You can copy the complete VBA code below to save your Excel chart as an image. Always back up your work before running any VBA code.
Option Explicit
Sub ExportMyChart()
'Create variables to hold the path and name of image
Dim myimagePath As String
Dim cht As Chart
'Change this location as required
myimagePath = "C:\Users\UserName\Documents\myImage.png"
Set cht = ActiveChart
'Export the chart, save Excel chart as an image
cht.Export (imagePath)
End Sub
Step 1. Name The Macro.
The first step is to name your macro once you have inserted a new module. I have named the macro ExportMyChart. Notice that Excel will insert the End Sub line of code once you have named the macro. The rest of the code should be inserted between these two lines.
Sub ExportMyChart()
End Sub
Step 2. Declare Variables.
Next, any variables for the macro need to be declared. This simply creates a space in Excel’s memory to store the values. In this example, there are two variables. The two variables are the name of the image once exported and the file path location of the exported chart.
Dim myimagePath As String
Dim cht As Chart
Step 3. Set The Location To Export The Chart.
The third step is to set the location of the exported chart. Just go ahead and change this to suit your own requirements.
'Change this location as required
myimagePath = "C:\Users\UserName\Documents\myImage.png"
Set cht = ActiveChart
Step 4. Export The Active Chart And Save As An Image.
This line of code exports the active chart to the location and name specified in Step 3.
'Export the chart
cht.Export (imagePath)
Step 5. Ending The Macro.
Once the chart has been exported then the macro ends. Excel already has this line of code from the beginning of the macro. It is now time to test the Excel Macro. Does it work?. Let me know in the comments below if you have used this code to save your Excel chart as an image.
If you want to read all of the Macro Monday blog posts in the series then bookmark the #MacroMonday page. If you want to also read all of the #FormulaFriday blog posts feel free to read and book mark that too.
More Excel Tips.
