Change scale of a embedded excel chart in powerpoint using VBA

  • Thread starter Thread starter Ram Chepyala
  • Start date Start date
R

Ram Chepyala

Hi Steve
I am having problems changing the scale of the embedded excel chart(XY
scatter) in PPT. Below is the code i am using
i have renamed the object to chart
Set oPPTShape = oPPTFile.Slides(mySlideNum).Shapes("chart")

Dim oxl As Excel.Workbook
Dim xchart As Excel.Chart
Dim xlsheet As Excel.Worksheet

Set oxl = oPPTShape.OLEFormat.Object
Set xchart = oxl.Charts(1)
'Set OAxes = xchart.Axes
Set xlsheet = oxl.Worksheets(1)
xlsheet.Cells(21, 7) = 10
xlsheet.Cells(22, 7) = -3
xlsheet.Cells(21, 8) = 15
xlsheet.Cells(22, 8) = -8
xlsheet.Cells(21, 9) = 14
xlsheet.Cells(22, 9) = 11
xlsheet.Cells(21, 10) = -10
xlsheet.Cells(22, 10) = -3
xlsheet.Cells(21, 11) = -14
xlsheet.Cells(22, 11) = 30

Cells(16, 15).FormulaR1C1 = "=MAX(R[-13]C:R[-5]C)"
Cells(17, 15).FormulaR1C1 = "=MIN(R[-13]C:R[-5]C)"
tempval = Round(Abs(Cells(16, 15)), 0)
If tempval <> "" Then
If tempval < 10 Then tempval = 10
Else
tempval = 10
End If
Set OAxes = xchart.Axes(xlCategory)
OAxes.MinimumScale = -30
OAxes.MaximumScale = 30
With OAxes
.MinimumScale = -1 * 1.5 * tempval
.MaximumScale = 1.5 * tempval
End With
Cells(16, 33).FormulaR1C1 = "=MAX(R[-13]C:R[-5]C)"
Cells(17, 33).FormulaR1C1 = "=MIN(R[-13]C:R[-5]C)"
tempval = Round(Abs(Cells(16, 33)), 0)
If tempval <> "" Then
If tempval < 10 Then tempval = 10
Else
tempval = 10
End If
Set OAxes = xchart.Axes(xlValue)
With OAxes
.MinimumScale = -1 * 1.5 * tempval
.MaximumScale = 1.5 * tempval
End With
oxl.Save
Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing

The scale does not change at all. I use the same code to change the
scale of bar charts in the ppt(not embedded) and works fine.

Many thanks in advance
 
Hi Ram,

I must have missed this somehow ... sorry for the late reply.

Suggestion:

Start with the same chart in Excel and record a macro while you make the
changes you want manually. That might point out whatever subtle difference is
causing this to fail.



Hi Steve
I am having problems changing the scale of the embedded excel chart(XY
scatter) in PPT. Below is the code i am using
i have renamed the object to chart
Set oPPTShape = oPPTFile.Slides(mySlideNum).Shapes("chart")

Dim oxl As Excel.Workbook
Dim xchart As Excel.Chart
Dim xlsheet As Excel.Worksheet

Set oxl = oPPTShape.OLEFormat.Object
Set xchart = oxl.Charts(1)
'Set OAxes = xchart.Axes
Set xlsheet = oxl.Worksheets(1)
xlsheet.Cells(21, 7) = 10
xlsheet.Cells(22, 7) = -3
xlsheet.Cells(21, 8) = 15
xlsheet.Cells(22, 8) = -8
xlsheet.Cells(21, 9) = 14
xlsheet.Cells(22, 9) = 11
xlsheet.Cells(21, 10) = -10
xlsheet.Cells(22, 10) = -3
xlsheet.Cells(21, 11) = -14
xlsheet.Cells(22, 11) = 30

Cells(16, 15).FormulaR1C1 = "=MAX(R[-13]C:R[-5]C)"
Cells(17, 15).FormulaR1C1 = "=MIN(R[-13]C:R[-5]C)"
tempval = Round(Abs(Cells(16, 15)), 0)
If tempval <> "" Then
If tempval < 10 Then tempval = 10
Else
tempval = 10
End If
Set OAxes = xchart.Axes(xlCategory)
OAxes.MinimumScale = -30
OAxes.MaximumScale = 30
With OAxes
.MinimumScale = -1 * 1.5 * tempval
.MaximumScale = 1.5 * tempval
End With
Cells(16, 33).FormulaR1C1 = "=MAX(R[-13]C:R[-5]C)"
Cells(17, 33).FormulaR1C1 = "=MIN(R[-13]C:R[-5]C)"
tempval = Round(Abs(Cells(16, 33)), 0)
If tempval <> "" Then
If tempval < 10 Then tempval = 10
Else
tempval = 10
End If
Set OAxes = xchart.Axes(xlValue)
With OAxes
.MinimumScale = -1 * 1.5 * tempval
.MaximumScale = 1.5 * tempval
End With
oxl.Save
Set xlsheet = Nothing
Set xchart = Nothing
Set oxl = Nothing

The scale does not change at all. I use the same code to change the
scale of bar charts in the ppt(not embedded) and works fine.

Many thanks in advance
Click to expand...
 
Back
Top