I have a chart in Excel which consists of a chart object called "Diagram". On top of this are added som text boxes with explanatory text. When I output with the Chart.Export method, the chart is exported in high quality as a .png file, BUT the text boxes are ignored and do not show up in the output.
The only way I have found to output the chart WITH the text boxes is to copy and paste the chart area and output what's pasted. This outputs everything, but the quality is quite a bit lower than when I export the original chart.
Is there a way to output the chart WITH the text boxes (I tried several ways of grouping the obejcts to no avail) without having to select and paste the original chart with lower resolution as a result?
Note: The chart is generated using data from a Cube that is also used for a Power BI solution, but I would still expect the chart to behave like any other chart? I don't know much about Cubes and how Excel connects to those.
This is the simple method that outputs chart WITHOUT text boxes but in high quality:
Sub exportCharts()
'dimension and set objects
Dim endFileName As String
Dim DagsDato As String
Dim OutputChart As ChartObject
Dim origHeight As Integer, origWidth As Integer
Set OutputChart = ActiveSheet.ChartObjects(1)
endFileName = "filepath.png"
OutputChart.Chart.Export endFileName
End Sub
And this is the method that outputs a copy of the chart WITH text boxes but in lower quality:
Sub ExportShapeAsImage()
Dim ws As Worksheet
Dim myShape As Shape
Dim tempChart As ChartObject
Dim exportPath As String
Dim mappe As String
Set ws = ActiveSheet
Set myShape = ws.Shapes("Diagram")
'Create temporary chart with same dimensions as the shape
Set tempChart = ws.ChartObjects.Add(myShape.Left, myShape.Top, myShape.Width, myShape.Height)
' Copy the shape as picture and paste to chart
myShape.CopyPicture Appearance:=xlScreen
tempChart.Select
tempChart.Chart.Paste
' Set export path and export as image
exportPath = "filepath.png"
tempChart.Chart.Export Filename:=exportPath, FilterName:="PNG"
' Clean up by deleting the temporary chart
tempChart.Delete
Set tempChart = Nothing
End Sub