Macro to close embedded charts/resize in PowerPoint

B

Barb Reinhardt

I have an embedded Excel chart in PowerPoint (I'm using 2003 if it makes a
difference) and am trying to select the Chart in the workbook to show when
it's closed. THe workbook has a chart sheet and a worksheet. There is
nothing else.

THis is the code I'm using

Sub CloseTopLeft()
Call CloseCharts("TL")
End Sub
Sub CloseBottomLeft()
Call CloseCharts("BL")
End Sub
Sub CloseCharts(myString As String)

Dim aWB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim myShape As PowerPoint.Shape
Dim PPTApp As PowerPoint.Application
Dim myPPT As PowerPoint.Presentation
Dim mySlide As PowerPoint.Slide
Dim myCht As Excel.Chart

Set PPTApp = GetObject(, "Powerpoint.Application")
Set myPPT = PPTApp.ActivePresentation
Set mySlide = GetActiveSlide(PPTApp.ActiveWindow)

Set aWB = ActiveWorkbook

For Each myCht In aWB.Charts
Debug.Print myCht.CodeName
If myCht.CodeName = "Chart1" Then
myCht.Select 'Correct chart is displayed.
Exit For
End If
Next myCht

aWB.Close
'After the workbook is closed, the worksheet is the "active sheet"
'shown in PowerPoint. I'm not sure why this is happening.
'Can someone assist?

For Each myShape In mySlide.Shapes
If myShape.Name Like "Object*" Then 'Will probably check for Excel
sheet
If myString = "TL" Then
If myShape.Top < 280 And myShape.Left < 300 Then
With myShape
.Left = 35.88
.Top = 107.88
.Height = 2.72 * 72
.Width = 4.52 * 72
End With
Exit For
End If
ElseIf myString = "BL" Then
If myShape.Top > 280 And myShape.Left < 300 Then
With myShape
.Left = 35.88
.Top = 4.25 * 72
.Height = 2.72 * 72
.Width = 4.52 * 72
End With
Exit For
End If
End If
End If
Next myShape

End Sub

Function GetActiveSlide(ByVal Wnd As Object) As Slide
Dim Sld As Slide

Select Case TypeName(Wnd)
Case "DocumentWindow"
Set Sld = Wnd.Selection.SlideRange(1)
Case "SlideShowWindow"
Set Sld = Wnd.View.Slide
End Select

Set GetActiveSlide = Sld
End Function

Thanks,
Barb Reinhardt
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top