set calculation property in embedded excel object

[erickd]

I am trying to set the application.calculation property in an embedded excel workbook object in a powerpoint file using a powerpoint vba macro. With the code below I am receiving the error:

'Unable to set the Calculation property of the Application class'

This would really help performance if I can get this to work.

Public oPres As Object
Public osld As Object
Public oshp As Object
Public total As Integer
Public count As Long
Public oxlbook As Object
Public oxlapp As Object
Public oxlchtsht As Object
Public oxldatsht As Object
Public oxlqrysht As Object
Public oxlqt As Object
--------------------------------------------------------------
Public Sub update_ppt()

Set oPres = ActivePresentation
total = ActivePresentation.Slides.count
count = 0
sttime = Now
progressForm.Show

    With oPres
        For Each osld In .Slides
            count = count + 1
        
            Call chtupdate(osld)
            
            Call UpdateProgress
        Next osld
    
    End With
progressForm.Hide

endtime = Now
elptime = (sttime - endtime)
tottime = Format(elptime, "nn:ss")
msg = "Update Done" & vbCrLf & vbCrLf
msg = msg & "Elapsed Time: " & tottime
MsgBox msg

End Sub
-----------------------------------------------------------
Private Sub chtupdate(oSlide As Object)
    
For Each oshp In oSlide.Shapes
    If oshp.Type = msoEmbeddedOLEObject Then
        Set oxlbook = oshp.OLEFormat.Object
        Set oxlchtsht = oxlbook.Worksheets("charts")
        Set oxldatsht = oxlbook.Worksheets("data")
        Set oxlqrysht = oxlbook.Worksheets("query")

        oxlbook.Application.Calculation = xlCalculationManual

        For Each oxlqt In oxlqrysht.QueryTables
            oxlqt.Refresh BackgroundQuery:=False
        Next oxlqt

        oxlbook.Application.Calculation = xlCalculationAutomatic

        DoEvents

    End If

    Set oxlqt = Nothing
    Set oxlbook = Nothing
        
    Next oshp

End Sub

Thanks

Erick

[romperstomper]

Does it help if you use:

        oxlbook.Application.Calculation = -4135

        For Each oxlqt In oxlqrysht.QueryTables
            oxlqt.Refresh BackgroundQuery:=False
        Next oxlqt

        oxlbook.Application.Calculation = -4105

[erickd]

unfortunately it does not, same error using the number codes

[romperstomper]

Curious - it just worked for me in a quick test.

[erickd]

I would believe it, this has worked once or twice, but it will not work consistently. I believe it has something to do with recognizing the Excel application object from the embedded object.

In other posts that I have read, they mention a workbook needs to be active. I tried oxlbook.Activate, but it did not make a difference. The rest of the code works fine for accessing and manipulating the Excel workbook object.

Did you use early or late binding?

Erick

[romperstomper]

Late binding - I assume you were early binding, or your code shouldn't ever work.
You might try:

oshp.OLEFormat.Activate

first?

[erickd]

well that is interesting, I believe I am using late binding as seen in the original post. I put in the

oshp.OLEFormat.Activate

and that broke the line in the 'For Each' statement with an application-defined or object-defined error and has the object opened.

For Each oxlqt In oxlqrysht.QueryTables
            oxlqt.Refresh BackgroundQuery:=False
        Next oxlqt

This has always run without needing to open the individual objects.

At least the calculation line completed.

[romperstomper]

If you were late binding originally, then xlCalculationAutomatic and xlCalculationManual would have had no values (as they are constants from the Excel object library, which you hadn't referenced, or declared anywhere that I can see) so those lines should never have worked (unless you had an error handler suppressing that).

[erickd]

ok, that is good to know.

I have the Excel 11.0 Object Library checked in the references of my VB editor. How do I declare the values?

[romperstomper]

If you have the reference set, you don't need to declare them (nor is there any real point in late binding!)

[erickd]

Ok I tried this vanilla code and get the same error. Can you replicate it?

Sub btest()

Dim oxlbook As Excel.Workbook
Dim oshp As Shape
Dim osld As Slide

Set osld = ActivePresentation.Slides(1)

For Each oshp In osld.Shapes
If oshp.Type = msoEmbeddedOLEObject Then
Set oxlbook = oshp.OLEFormat.Object

oxlbook.Application.Calculation = xlCalculationManual

MsgBox "Manual"

oxlbook.Application.Calculation = xlCalculationAutomatic

MsgBox "Automatic"

End If
Next oshp

End Sub

Thanks for your help on this.

[erickd]

ok, some developments.

Using the vanilla code in my previous post, I can make it work IF:

I have excel open with any spreadsheet open (even unsaved blank). Then insert an excel object on to the ppt slide.

If you then close excel or just close the open spreadsheet, it will not work.

I was running the code usually with Excel closed are empty (without a spreadsheet).

At least now I have a work around, will continue to work on it.