F
Frank Hayes
Jon Peltier wrote a great macro to copy an Excel Chart on a worksheet (not a
chart sheet) to an open PowerPoint presentation. I had need of a slightly
different version and modified the code to the state below to a) work with a
Chart sheet and b) preserve the size of the title font on the recopy.
My issue is that my original chart sheet is driven via a combo box. I
choose an item in the combo box (e.g. "Asia" or "Europe") and the chart
automatically redisplays with that data. My chart title also changes
dynamically depending on the combo box choice, i.e, the chart title is
"=DataSheet!$B$5". When the macro below executes, it pastes a static
version of the title text back into the excel chart sheet instead of the
formula.. Can someone help me with the code to copy the dynamic link back
instead, such that the next time I change the combo box, the title will
refresh as well?
I would also like this single macro to work with charts where the title is
text, not a formula.
Thanks for any help
Frank
Sub SingleChartAndTitleToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library
' This is a modification of code written by Jon Peltier found at
' http://peltiertech.com/Excel/XL_PPT.html
' the modification was made to work with a chart on a chart sheet instead of
' a chart in a worksheet. In addition, the code was changed to restore the
' title font size back to its original size instead of the default.
' Modified by Frank Hayes
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle
Dim sTitleSize As Integer
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
With ActiveChart
' get chart title
If .HasTitle Then
sTitle = .ChartTitle.Text
sTitleSize = .ChartTitle.Font.Size
Else
sTitle = ""
End If
' remove title (or it will be redundant)
.HasTitle = False
' copy chart as a picture
.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
' restore title
If Len(sTitle) > 0 Then
.HasTitle = True
.ChartTitle.Text = sTitle
.ChartTitle.Font.Size = sTitleSize
End If
End With
' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
.Shapes.Placeholders(1).TextFrame.TextRange.Text = sTitle
End With
MsgBox "Chart Copied To PowerPoint Presentation"
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub
chart sheet) to an open PowerPoint presentation. I had need of a slightly
different version and modified the code to the state below to a) work with a
Chart sheet and b) preserve the size of the title font on the recopy.
My issue is that my original chart sheet is driven via a combo box. I
choose an item in the combo box (e.g. "Asia" or "Europe") and the chart
automatically redisplays with that data. My chart title also changes
dynamically depending on the combo box choice, i.e, the chart title is
"=DataSheet!$B$5". When the macro below executes, it pastes a static
version of the title text back into the excel chart sheet instead of the
formula.. Can someone help me with the code to copy the dynamic link back
instead, such that the next time I change the combo box, the title will
refresh as well?
I would also like this single macro to work with charts where the title is
text, not a formula.
Thanks for any help
Frank
Sub SingleChartAndTitleToPresentation()
' Set a VBE reference to Microsoft PowerPoint Object Library
' This is a modification of code written by Jon Peltier found at
' http://peltiertech.com/Excel/XL_PPT.html
' the modification was made to work with a chart on a chart sheet instead of
' a chart in a worksheet. In addition, the code was changed to restore the
' title font size back to its original size instead of the default.
' Modified by Frank Hayes
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim PresentationFileName As Variant
Dim SlideCount As Long
Dim iCht As Integer
Dim sTitle
Dim sTitleSize As Integer
' Reference existing instance of PowerPoint
Set PPApp = GetObject(, "Powerpoint.Application")
' Reference active presentation
Set PPPres = PPApp.ActivePresentation
PPApp.ActiveWindow.ViewType = ppViewSlide
With ActiveChart
' get chart title
If .HasTitle Then
sTitle = .ChartTitle.Text
sTitleSize = .ChartTitle.Font.Size
Else
sTitle = ""
End If
' remove title (or it will be redundant)
.HasTitle = False
' copy chart as a picture
.CopyPicture _
Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
' restore title
If Len(sTitle) > 0 Then
.HasTitle = True
.ChartTitle.Text = sTitle
.ChartTitle.Font.Size = sTitleSize
End If
End With
' Add a new slide and paste in the chart
SlideCount = PPPres.Slides.Count
Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutTitleOnly)
PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
With PPSlide
' paste and select the chart picture
.Shapes.Paste.Select
' align the chart
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
.Shapes.Placeholders(1).TextFrame.TextRange.Text = sTitle
End With
MsgBox "Chart Copied To PowerPoint Presentation"
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End Sub