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
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