VB PPT findiing max value in an embedded Excel worksheet

[lance-news]

Anyone have any ideas how I can find the maximum value of the range
B3:O3 in an embedded Excel sheet within PPT? I don't want to create
a cell formula is possible. I simply wish to save the max value within
the macro. Are there any commands that will allow me to do this?

Sub test()

Dim SlideObject As Slide
Dim ShapeObject As Shape
For Each SlideObject In Application.ActivePresentation.Slides
For Each ShapeObject In SlideObject.Shapes

If ShapeObject.Type = 7 Then
' I know this won't work, it is simply an example of what I am
' trying to accomplish

MaxVal = Max(B3:O3)

End If
Next ShapeObject
Next SlideObject
End Sub

 

[Guest]

Lance
Here is an example of what you are trying to do..

' ========================
Dim SlideObject As Slid
Dim ShapeObject As Shap
For Each SlideObject In Application.ActivePresentation.Slide
For Each ShapeObject In SlideObject.Shape

If ShapeObject.Type = msoEmbeddedOLEObject The
' Perform a check to ensure that the object is an Excel object prior to this..
' Left as exercise..

' Extract the max value in a given range on the embedded sheet
Dim oXLBook As Excel.Workboo
Set oXLBook = ShapeObject.OLEFormat.Objec
With oXLBoo
Debug.Print .Application.WorksheetFunction.Max(.ActiveSheet.Range("B3:O3")
End Wit
End I
Next ShapeObjec
Next SlideObjec
'=================================
Regard
Shyam Pilla

http://www.mvps.org/sk

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

Anyone have any ideas how I can find the maximum value of the rang
B3:O3 in an embedded Excel sheet within PPT? I don't want to creat
a cell formula is possible. I simply wish to save the max value withi
the macro. Are there any commands that will allow me to do this

Sub test(

Dim SlideObject As Slid
Dim ShapeObject As Shap
For Each SlideObject In Application.ActivePresentation.Slide
For Each ShapeObject In SlideObject.Shape

If ShapeObject.Type = 7 The
' I know this won't work, it is simply an example of what I a
' trying to accomplis

MaxVal = Max(B3:O3

End I
Next ShapeObjec
Next SlideObjec
End Su

 

[lance-news]

Hello,

Still having some problems with this snipit of code.
I guess my main problem is that I don't really know
how the WITH method works so I am at a loss.

Any help would be appreciated.

Lance



Sub maxval()


Dim SlideObject As Slide
Dim ShapeObject As Shape
For Each SlideObject In Application.ActivePresentation.Slides
For Each ShapeObject In SlideObject.Shapes

If ShapeObject.Type = msoEmbeddedOLEObject Then




If ShapeObject.OLEFormat.ProgID = "Excel.Sheet" Then

' Extract the max value in a given range on the embedded sheet.
Set oXLBook = ShapeObject.OLEFormat.Object
Set oXLSheet = oXLBook.Worksheets(1)

With oXLSheet

' maxval = 0.5
' Range("C5").Value = maxval

MaxVal = .Application.WorksheetFunction.Max(Range("B3:F3"))
Range("C5").Value = Maxval

End With



End If





End If
Next ShapeObject
Next SlideObject

End Sub

 

[Steve Rindsberg]

Hello,

Still having some problems with this snipit of code.

What problems, specifically?

 

[Keith R]

Definitely not an expert, but have you tried:

MaxVal = Application.WorksheetFunction.Max(Range("B3:F3"))

..Range("C5").Value = Maxval

not tested, just thinking that the range value is related to your "with",
e.g. (Worksheet).range
but the application statement isn't
(Worksheet).Application.Worksheetfunction.Etc

 

[lance-news]

Well, when I run I get a compile error at

..Max(Range("B3:F3"))

with "Sub or Function not defined"

 

[Steve Rindsberg]

Well, when I run I get a compile error at

..Max(Range("B3:F3"))

with "Sub or Function not defined"

Mixing up your code and Shyam's and stirring well, seasoning to taste, I
get this, which works here (watch out for linebreaks!):

Sub maxval()

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
With oXLBook
maxval =
Application.WorksheetFunction.Max(.ActiveSheet.Range("B3:F3"))
.ActiveSheet.Range("C5").Value = maxval
End With
End If
End If
Next ShapeObject
Next SlideObject
End Sub