Microsoft Office Tutorials and References
In Depth Information
Creating a PPT Presentation from a Pivot Chart
Dim Pivot As PivotTable¶
Dim PItem As PivotItem¶
Dim PivotData As Range¶
Dim Avg As Double¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'Set a reference to the Pivot Chart¶
Set Cht = ActiveSheet.ChartObjects(1).Chart¶
'Set a reference to the PivotTable of the chart¶
Set Pivot = Cht.PivotLayout.PivotTable¶
'Set a reference to the database (using an Excel database)¶
Set PivotData = Range(Application.ConvertFormula( _¶
Pivot.SourceData, xlR1C1, xlA1))¶
'Create a new PowerPoint presentation (and application)¶
'New PowerPoint.Application¶
Set pPoint = CreateObject("PowerPoint.Application")¶
Set pPres = pPoint.Presentations.Add¶
'Loop through each of the items of the page field¶
For Each PItem In Pivot.PageFields(1).PivotItems¶
'Check for (All)¶
If PItem.Caption <> "(All)" Then¶
'Any slides yet ?¶
Pivot.PageFields(1).CurrentPage = PItem.Caption¶
If pPres.Slides.Count = 0 Then¶
Set pSlide = pPres.Slides.Add(1, 12) 'ppLayoutBlank = 12¶
Else¶
Set pSlide = pPres.Slides.Add(pPres.Slides.Count + 1, _¶
12) 'ppLayoutBlank¶
End If¶
'Copy an image of the chart¶
Cht.CopyPicture Appearance:=xlPrinter, Format:=xlPicture¶
'Paste it in PowerPoint¶
Set pShape = pSlide.Shapes.Paste¶
'Resize it¶
With pShape¶
.ScaleWidth 1, msoFalse, msoScaleFromTopLeft¶
.ScaleHeight 0.94, msoFalse, msoScaleFromBottomRight¶
.Left = 10¶
.Top = 10¶
End With¶
'Create the average of this region¶
Set pAvg = pSlide.Shapes.AddLabel( _¶
Orientation:=msoTextOrientationHorizontal, _¶
Left:=pShape.Left, Top:=pShape.Top + pShape.Height + 10, _¶
Width:=pShape.Width, Height:=30)¶
'Calculate the average¶
Exl
Search JabSto ::




Custom Search