PPT VBA msoEmbeddedObjected adding autoshapes

[lance-news]

So, I finally am able to retrieve the Maximum value from a Range
in an Embedded Excelsheet. I now have a question regarding Autoshapes.
It is very tedious to find the location in each position for each
autoshape.

Is it possible to specify something like,

add a cirlce that is centered across cells ("B3:C3")

so that I don't have to manually center?
My cells are merged so that B3:C3 are one cell and D3:E3
are one cell ...


Also note that the autoshapes are being inserted on top
of the XL embedded sheet and not directly inside the XL
embedded sheet.

Lance





Sub maxval2()

Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet

Dim SlideObject As Slide
Dim ShapeObject As Shape

Dim maxval As Variant

For Each SlideObject In Application.ActivePresentation.Slides
For Each ShapeObject In SlideObject.Shapes
If ShapeObject.Type = msoEmbeddedOLEObject Then
If Mid$(ShapeObject.OLEFormat.ProgID, 1, 11) =
"Excel.Sheet" Then
Set oXLBook = ShapeObject.OLEFormat.Object

myRange = oXLBook.Worksheets("Sheet1").Range("B3:O3")
Maxanswer = oXLBook.Application.WorksheetFunction.Max(myRange)
'MsgBox (answer)
myRange1 = oXLBook.Worksheets("Sheet1").Range("B3").Value
myRange2 = oXLBook.Worksheets("Sheet1").Range("D3").Value
myRange3 = oXLBook.Worksheets("Sheet1").Range("F3").Value
myRange4 = oXLBook.Worksheets("Sheet1").Range("H3").Value
myRange5 = oXLBook.Worksheets("Sheet1").Range("J3").Value
myRange6 = oXLBook.Worksheets("Sheet1").Range("L3").Value
myRange7 = oXLBook.Worksheets("Sheet1").Range("N3").Value

If (myRange1 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 225, 180,
36, 36).Select
If (myRange2 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 286, 180,
36, 36).Select
If (myRange3 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 345, 180,
36, 36).Select
If (myRange4 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 405, 180,
36, 36).Select
If (myRange5 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 465, 180,
36, 36).Select
If (myRange6 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 525, 180,
36, 36).Select
If (myRange7 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 585, 180,
36, 36).Select

 

[Guest]

Lance
It's more difficult to draw a shape around a cell range in PowerPoint because you don't have access to the coordinate locations of the cells. It's easier to have it drawn in Excel

Regard
Shyam Pilla
http://www.mvps.org/sk

----- (e-mail address removed) wrote: ----

So, I finally am able to retrieve the Maximum value from a Rang
in an Embedded Excelsheet. I now have a question regarding Autoshapes
It is very tedious to find the location in each position for each
autoshape

Is it possible to specify something like

add a cirlce that is centered across cells ("B3:C3"

so that I don't have to manually center
My cells are merged so that B3:C3 are one cell and D3:E
are one cell ..


Also note that the autoshapes are being inserted on to
of the XL embedded sheet and not directly inside the X
embedded sheet

Lanc





Sub maxval2(

Dim oXLBook As Excel.Workboo
Dim oXLSheet As Excel.Workshee

Dim SlideObject As Slid
Dim ShapeObject As Shap

Dim maxval As Varian

For Each SlideObject In Application.ActivePresentation.Slide
For Each ShapeObject In SlideObject.Shape
If ShapeObject.Type = msoEmbeddedOLEObject The
If Mid$(ShapeObject.OLEFormat.ProgID, 1, 11) =
"Excel.Sheet" The
Set oXLBook = ShapeObject.OLEFormat.Objec

myRange = oXLBook.Worksheets("Sheet1").Range("B3:O3"
Maxanswer = oXLBook.Application.WorksheetFunction.Max(myRange
'MsgBox (answer
myRange1 = oXLBook.Worksheets("Sheet1").Range("B3").Valu
myRange2 = oXLBook.Worksheets("Sheet1").Range("D3").Valu
myRange3 = oXLBook.Worksheets("Sheet1").Range("F3").Valu
myRange4 = oXLBook.Worksheets("Sheet1").Range("H3").Valu
myRange5 = oXLBook.Worksheets("Sheet1").Range("J3").Valu
myRange6 = oXLBook.Worksheets("Sheet1").Range("L3").Valu
myRange7 = oXLBook.Worksheets("Sheet1").Range("N3").Valu

If (myRange1 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 225, 180,
36, 36).Selec
If (myRange2 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 286, 180,
36, 36).Selec
If (myRange3 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 345, 180,
36, 36).Selec
If (myRange4 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 405, 180,
36, 36).Selec
If (myRange5 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 465, 180,
36, 36).Selec
If (myRange6 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 525, 180,
36, 36).Selec
If (myRange7 = Maxanswer) Then SlideObject.Shapes.AddShape(9, 585, 180,
36, 36).Selec

 

[lance-news]

Shyam,

Are you indicating that this is extremely difficult to accomplish in PPT
in any form or fashion, or, are you indicating that I should draw a
shape within the Excel embedded object which will allow me to access the
coordinate locations of the cells?

I ask because it is my understanding that using an embedded object
(Excel) in PPT implies that when I embed an Excel object in PPT I am
actually able to access the Excel program within PPT? This means,
theoretically, I could access the coordinate locations of the cells
using Excel even though I am in PPT. I say this because I have noticed
that when I open an embedded XL object and record a macro that the macro
is actually recorded in Excel and not in PPT.

Being a newbie I could be completely wrong in this??

Lance

Lance,
It's more difficult to draw a shape around a cell range in PowerPoint because you don't have access to the coordinate

locations of the cells. It's easier to have it drawn in Excel.