Managing Embedded Charts In Excel VBA

Similar to the previous section 7.3.1, Managing Chart Sheets, here are three procedures for copying, deleting, and exporting an embedded chart.

Copying an Embedded Chart:

Sub CopyEmbeddedChart()
    With ThisWorkbook.Worksheets("Sheet1")
        .ChartObjects(1).Copy
        .Paste
        .ChartObjects(2).Top = 250
        .ChartObjects(2).Left = 200
    End With
End Sub

Explanation:
It is assumed that the worksheet « Sheet1 » initially contains only one chart frame. This chart frame is indexed as ChartObjects(1).

The Copy() method copies this chart frame (including its chart) to the clipboard.

The Paste() method pastes the copied chart frame back into the same worksheet, creating a new chart frame, which becomes ChartObjects(2).

The position of this new chart frame is set by modifying its Top and Left properties, placing it further down and to the right within the worksheet.

Deleting an Embedded Chart:

Sub DeleteEmbeddedChart()
    ThisWorkbook.Worksheets("Sheet1").ChartObjects(2).Delete
End Sub

Explanation:
The Delete() method removes the specified chart frame (here, the second chart frame on « Sheet1 ») without prompting for confirmation.

Exporting an Embedded Chart as an Image:

Sub ExportEmbeddedChart()
    ThisWorkbook.Worksheets("Sheet1").ChartObjects(1).Chart.Export "C:\Temp\April.jpg"
End Sub

Explanation:
The Export() method creates an image file from the chart contained within the specified chart frame. Here, it exports the chart from the first embedded chart frame as a .jpg file to the path C:\Temp\April.jpg.