Resize a Chart Object with Excel VBA

Question

I want to resize a Chart object. I am creating a graph from a set of data in Excel and inserting the graph in a Word doc via VBA.

I tried to use .Width property from the ChartObjects object but I couldn't extend this to chart. ( I got an application error with ws1.ChartObjects.)

Sub CPU_Top_10_Chart()

Dim wdApp   As Word.Application
Dim wdDoc   As Word.Document
Dim cht     As Chart
Dim ws1     As Worksheet

 Set wdApp = CreateObject("Word.Application")
 Set wdDoc = wdApp.Documents.Add("C:\...\chart_test.docx")

 Set cht = Charts.Add
 Set ws1 = ActiveWorkbook.Sheets("Sheet1")

 With cht

        .SetSourceData Source:=ws1.Range("A2:C12")
        .ChartType = xlColumnClustered

End With

cht.CopyPicture
wdDoc.Bookmarks("insert_chart").Range.Paste
End Sub

Seems, you're trying to paste image into word document instead of linked chart. — Maciej Los
– Maciej Los, Commented May 4, 2016 at 19:10
Yes, I am trying to do this with VBA. But I want to resize the Chart. — Concerned_Citizen
– Concerned_Citizen, Commented May 4, 2016 at 19:15

Florent B. · Accepted Answer · 2016-05-04 20:38:31Z

2

You could call "Worksheet.Shapes.AddChart" to create the chart and set the size:

With ActiveSheet.Shapes.AddChart(xlColumnClustered, Width:=100, Height:=100)
  With .Chart
    .SetSourceData ws1.Range("A2:C12")
  End With
  .CopyPicture
End With

edited May 4, 2016 at 20:38

answered May 4, 2016 at 20:30

Florent B.

42.7k7 gold badges92 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Concerned_Citizen Over a year ago

So don't use Chart and just try it with Shapes object?

Florent B. Over a year ago

A Chart is a Shape and some properties like the height and width are only available on the Shape.

Jon Peltier Over a year ago

The chart is contained within a special shape called a chartobject. The chartobject has all the properties of a shape. You can access the chartobject using chart.parent.

Concerned_Citizen · Accepted Answer · 2016-05-04 21:00:49Z

0

This is what I added

Set cht = ws1.Shapes.AddChart(xlColumnClustered, Width:=300, Height:=300).Chart

answered May 4, 2016 at 21:00

Concerned_Citizen

6,89519 gold badges61 silver badges81 bronze badges

Collectives™ on Stack Overflow

Resize a Chart Object with Excel VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related