Export a group of graphic object (Chart + other lines) from Excel 2012

Question

I have VBA code that exports the active chart from Excel in PNG format.

I have some dots and lines, marking some important data overlaid on my Excel chart, and they are grouped (select all objects and chart, Right Click -> Group).

Is there anything that I can replace the ActiveChart with (like ActiveGroup or similar) to export the whole thing, not just the chart.

Sub ExportChartToPNG()
'Take ActiveChart and copy it as a GIF image to the same directory as the Workbook is in and name it with the Chart_Title with spaces replaced with underscores.
Dim chtCopyChart As Chart, sCurrentDirectory As String, sFileName As String
Dim x As Integer, CellCharacter As String
Dim sInteractive As Boolean

Set chtCopyChart = ActiveChart
sCurrentDirectory = ActiveWorkbook.Path
sFileName = chtCopyChart.ChartTitle.Text
sFileName = InputBox("Enter filename for export:", "Export object", sFileName)

For x = 1 To Len(sFileName)
    CellCharacter = Mid(sFileName, x, 1)
    If CellCharacter Like "[</*\?%]" Then
        sFileName = Replace(sFileName, CellCharacter, "_", 1) ', Replaces all illegal filename characters with "_"
    End If
If Asc(CellCharacter) <= 32 Then
    sFileName = Replace(sFileName, CellCharacter, "_", 1) ' Replaces all non printable characters with "_"
End If

Next

sFileName = sFileName & ".png"
sFileName = sCurrentDirectory & "\" & sFileName
sInteractive = True

chtCopyChart.Export Filename:=sFileName, FilterName:="PNG", Interactive:=sInteractive

MsgBox "Chart copied to " & sFileName, vbOKOnly, "Success!"

End Sub

Answer 1

Here is code that works to save an image of a group of shapes. It's a modification of Jeremy's answer, that finds a specific group (based on the [Alt Text] Title found under 'Format Shape'). The sub runs a specific macro first (to update the graph in the Group).

Global Const myFilePath = "C:\YourFolder\"    
Public Sub saveChart(ByVal sheetName As String, ByVal macroName As String, _
                            ByVal fileName As String, exportType As Integer)
        Dim wb As Workbook
        Dim ws As Worksheet
        Set wb = ThisWorkbook
        Set ws = Sheets(sheetName)
        ws.Activate
        Application.Run "'" & wb.Name & "'!VBAProject." & ws.CodeName & "." & macroName

        Select Case exportType
            Case 0 'standard chart
                Set objChrt = Sheets(sheetName).ChartObjects(1)
                Set myChart = objChrt.Chart
                myChart.Export fileName:=myFilePath & fileName, Filtername:="JPEG"
            Case 1 'Group of chart and other objects
                Dim sh As Shape
                Dim I As Integer
                Dim groupedName As String
                I = 1
                    'Find grouped shape in worksheet with Title of 'Export'
                For Each sh In ActiveSheet.Shapes
                    If sh.Type = 6 Then '6 indicates it's a group
                        If sh.Title = "Export" Then
                            Set myshape = sh
                            groupedName = sh.Name
                        End If
                    End If
                    I = I + 1
                Next
                    'Select and copy group
                ws.Shapes.Range(Array(groupedName)).Select
                Selection.CopyPicture
                    'Create temporary chart
                Set chtObj = ws.ChartObjects.Add( _
                            myshape.Left, myshape.Top, myshape.Width, myshape.Height)
                    'Select temporary chart and paste the Group
                chtObj.Select
                chtObj.Chart.Paste
                    'Export the image
                chtObj.Chart.Export fileName:=myFilePath & fileName, Filtername:="JPEG"
                    'Clean up
                chtObj.delete
                Set chtObj = Nothing
            Case Else
        End Select
        Set wb = Nothing
        Set ws = Nothing
    End Sub

Answer 2

Old question I know, but the solution comes from the fact that a chart grouped with other shapes becomes a shape object in the worksheet. So what you actually need to do is get a reference to the shape object which is the group you've created.

However, there's no export method on shapes, so you need to create a temporary blank chart, copy the shape into it, export the new chart, then delete it.

The steps are:

Get the shape object and copy it as a picture

set myshape = Sheet24.Shapes("shapename")
myshape.CopyPicture

Create a new chartobject with the same dimensions as the source shape

 set chtObj = Sheets24.ChartObjects.Add(myshape.Left, myshape.Top, myshape.Width, myshape.height)

Paste the object from the clipboard to the new chart

chtObj.Chart.Paste

Export the chart, deleting an existing file if needed

Kill fullpathandfilename    
chtObj.Chart.Export filename:=fullpathandfilename, Filtername:="PNG" 

Then delete the chart and clean up objects.

chtObj.Delete
Set chtObj = nothing