0

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
3
  • So the textboxes are not part of the chart but independent shapes on the sheet?
    – FunThomas
    Commented 9 hours ago
  • What about using CTRL / PRNTSCRN , paste the image in PAINT the crop it down to the size you want ?
    – Jerry
    Commented 4 hours ago
  • If you insert the textboxes into the chart (and not just onto the sheet) then they'll get exported along with the chart. Commented 36 mins ago

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.