Checking Powerpoint shape type programmatically from Excel

[JBSMichael]

Hello again, worthy gurus.

I have a macro that creates a Powerpoint slide to show graphically a list of records from Excel. The code builds a framework of rectangles in PP and then plots each record as a 6-point star in the relevant rectangle, depending on a value in the record. My issue is that I need to be able to work out if a star is about to be plotted on top of an existing star and, if it is, I want to shift the .top and .left values to offset the new star so that both are visible. My current code tries to do this (the following is within a For - Next loop that cycles through the records to be plotted):

        For Each shp In PPSlide.Shapes
            If shp.Type = msoShape6PointStar And shp.Left = l And shp.Top = t Then
                a = a + 3
            End If
        Next shp

        l = l + a
        t = t + a
        Set shpStar = PPSlide.Shapes.AddShape(msoShape6pointStar, l, t, 20, 20)

I have narrowed down the problem to the fact that Excel is not seeing an existing star shape as 'shp.Type=msoShae6PointStar'. However, I can't find a way to form the 'If shp.Type = msoShape6PointStar' phrase in a way that Excel will act on. I have tried shp.Type = 147, doesn't work.

Can anyone help, please?

Thanks in advance

Jimmy

[Bob Phillips]

Can you post your workbook?

[Charlize]

        For Each shp In PPSlide.Shapes
            If shp.Type = msoShape6PointStar And shp.Left = l And shp.Top = t Then
                a = a + 3
            End If
        Next shp

        l = l + a
        t = t + a
        Set shpStar = PPSlide.Shapes.AddShape(msoShape6pointStar, l, t, 20, 20)

If PPSlide is defined as a powerpoint object, ie a powerpoint presentation, you have to refer to the program powerpoint to use the variables of that program. To clarify, I can't use variables that are used in the excelprogram when I write coding in outlook when I don't use a reference to the excel objects.

Maybe if you define an object for the program powerpoint and use that object to reference to msoShape6PointStar .
If myPP is defined as object for the program powerpoint you could try myPP.msoShape6PointStar to reference this.

Just an idea off course.

[JBSMichael]

xld, thanks for the reply but I can't post the workbook as it is sensitive. If I have no joy I will take the time to create a declassified copy.

Charlize, again thank you but all the definitions are there - the PowerPoint app is launched okay, the file created okay, the slide and all the shape objects. The only thing I can't get it to do is a function that says 'is there a star in this position already? If so, position the new star a bit below and to the right of the existing one'. Infuriating.

[Charlize]

- You have to be sure that the shape, that you want to position, resides on the page that has the activeview in powerpoint. With other words, you can't alter shapes if the active view is a page that doesn't hold the shapes that you want to position on a page.

Sorry, bit confused, activeview is for publisher.

[Charlize]

This little example is a 1-slide presentation with 3 shapes on it. One of them is a 5-star-object. When you loop through the coding, you'll see that the .Name gives the name of the shape. Maybe you can use this name to locate the stars that you are looking for.

Sub test()
Dim myslide As Long
Dim myshape As Long
MsgBox ActivePresentation.Slides.Count
MsgBox ActivePresentation.Slides(1).Shapes.Count
For myslide = 1 To ActivePresentation.Slides.Count
    For myshape = 1 To ActivePresentation.Slides(myslide).Shapes.Count
        MsgBox ActivePresentation.Slides(myslide).Shapes(myshape).Type
        MsgBox ActivePresentation.Slides(myslide).Shapes(myshape).Name
    Next myshape
Next myslide
End Sub

[JBSMichael]

Thanks very much Charlize for your time and consideration. I was happy that the code was finding the right slide, and in some configurations was finding the right shapes but just not differentiating between them.

However, I found a solution all by my little self, which is:

        Set shpStar = PPSlide.Shapes.AddShape(msoShape6pointStar, l, t, x, x)        
        With shpStar
            .Line.ForeColor.RGB = RGB(255, 255, 255)
            .Line.Weight = 0.2
            .Fill.ForeColor.RGB = RGB(0, 0, 0)
            .Name = "star" & l & t & b
        End With

...

        For Each shp In PPSlide.Shapes
            If Left(shp.Name, Len(shp.Name) - 1) = Left(shpStar.Name, Len(shpStar.Name) - 1) Then
                shpStar.Left = shpStar.Left + a
                shpStar.Top = shpStar.Top + a
            End If
        Next shp
        Set shp = Nothing
        Set shpStar = Nothing
        b = b + 1

So it produces a name for each newly created star that includes the position of the shape and a sequential number - if the name minus the serial number matches, then the position shift fires.