Embedded Excel VBA in PowerPoint

[Alon]

Hi,

I've been working on developing solutions to automate some of my
presentations so that macros that I usually run in applications other
than PowerPoint (such as Excel for instance) can be run directly from
PowerPoint by embedding Excel object into my presentations.

I recently ran into an error in one of my VBA scripts that I just
can't figure out how to fix. The code is the following (title and rule
are passed in string variables), it is designed to add a slide to the
end of the presentation and then add an excel sheet onto it:


ActivePresentation.Slides.Add(Index:=(ActivePresentation.Slides.Count
+ 1), Layout:=ppLayoutBlank).Select
ActiveWindow.Selection.SlideRange.Shapes.AddOLEObject(Left:=120,
Top:=110, Width:=480, Height:=320, ClassName:="Excel.Sheet",
Link:=msoFalse).Select
With ActiveWindow.Selection.ShapeRange
.Name = title
With .OLEFormat.Object
.Sheets.Add.Name = "Data"
.Sheets("Sheet1").Name = "Config"
.Sheets("Config").Visible = False
.Sheets("Config").Range("A1").Value = title 'The name in
A1
.Sheets("Config").Range("A2").Value = rule 'Rules in A2
End With
End With

Even though everything runs and each step is executed, I get the
following error:
Runtime error(91): Object variable or with block variable not set

Any help would be greatly appreciated. I have tried to keep out any
code that isn't needed, but can provide more info if needed to give a
better understanding. Again, everything runs fine, but I still get
that annoying runtime error 'almost' each time, but I can't find a
pattern as to when it comes up and when it doesn't.

Thanks,
Alon

 

[Steve Rindsberg]

Hi,

I've been working on developing solutions to automate some of my
presentations so that macros that I usually run in applications other
than PowerPoint (such as Excel for instance) can be run directly from
PowerPoint by embedding Excel object into my presentations.

I recently ran into an error in one of my VBA scripts that I just
can't figure out how to fix. The code is the following (title and rule
are passed in string variables), it is designed to add a slide to the
end of the presentation and then add an excel sheet onto it:

Suggestion: never select anything unless it's utterly necessary.

Modifed as below, watch out for linebreaks:

Dim oSl as PowerPoint.Slide
Dim oSh as PowerPoint.Shape

Set oSl = ActivePresentation.Slides.Add _
(Index:=(ActivePresentation.Slides.Count + 1), Layout:=ppLayoutBlank)

Set oSh = oSl.Shapes.AddOLEObject(Left:=120,
Top:=110, Width:=480, Height:=320, ClassName:="Excel.Sheet",
Link:=msoFalse)

With oSh
.Name = title
With .OLEFormat.Object
.Sheets.Add.Name = "Data"
.Sheets("Sheet1").Name = "Config"
.Sheets("Config").Visible = False
.Sheets("Config").Range("A1").Value = title 'The name in A1
.Sheets("Config").Range("A2").Value = rule 'Rules in A2
End With
End With

Even though everything runs and each step is executed, I get the
following error:
Runtime error(91): Object variable or with block variable not set

At what line of code?

 

[Alon]

At what line of code?

The solution above could fix this error - I used the select methods
since most of my scripts are generated through macro recording - but I
can't give you a precise answer at what line the error occurs, it's
like a moving error if that makes sense. The error doesn't even make
sense b/c I use an error handler and it doesn't go to the error
handler, it just outputs that error and only allows me to select End
which doesn't end anything since the script keeps running afterwards.

 

[Alon]

The solution above could fix this error - I used the select methods
since most of my scripts are generated through macro recording - but I
can't give you a precise answer at what line the error occurs, it's
like a moving error if that makes sense. The error doesn't even make
sense b/c I use an error handler and it doesn't go to the error
handler, it just outputs that error and only allows me to select End
which doesn't end anything since the script keeps running afterwards.

I have tested this out and the error still appears but now I know at
what line:

Set oSh = oSl.Shapes.AddOLEObject(Left:=120,
Top:=110, Width:=480, Height:=320, ClassName:="Excel.Sheet",
Link:=msoFalse)

The program keeps running though and an Excel object is created with
the settings/sheets that I specify

 

[Steve Rindsberg]

I have tested this out and the error still appears but now I know at
what line:

Set oSh = oSl.Shapes.AddOLEObject(Left:=120,
Top:=110, Width:=480, Height:=320, ClassName:="Excel.Sheet",
Link:=msoFalse)

The program keeps running though and an Excel object is created with
the settings/sheets that I specify

I tried running this from within PPT and it seems to work as expected.
If not for you, try disabling your errorhandler and see if it breaks repeatedly
at the same point.

Dim oSl As PowerPoint.Slide
Dim oSh As PowerPoint.Shape

Set oSl = ActivePresentation.Slides.Add _
(Index:=(ActivePresentation.Slides.Count + 1), Layout:=ppLayoutBlank)

Set oSh = oSl.Shapes.AddOLEObject _
(Left:=120, Top:=110, Width:=480, Height:=320, _
ClassName:="Excel.Sheet", Link:=msoFalse)

With oSh
.Name = "Blah"
With .OLEFormat.Object
.Sheets.Add.Name = "Data"
.Sheets("Sheet1").Name = "Config"
.Sheets("Config").Visible = True
' instead of false so I can see the result

.Sheets("Config").Range("A1").Value = "blah" 'The name in A1
.Sheets("Config").Range("A2").Value = "dadarule" 'Rules in A2
End With
End With

Set oSl = Nothing
Set oSh = Nothing

 

[Alon]

I have figured out that the addition of a sheet is causing the error.
To fix this I just added a .Activate to activate the workbook and I
haven't seen the error since (freak accident fix).

Thanks for the help,
Alon

 

[Steve Rindsberg]

I have figured out that the addition of a sheet is causing the error.
To fix this I just added a .Activate to activate the workbook and I
haven't seen the error since (freak accident fix).

Odd ... it worked fine here, even with the addition of the sheet (though I set
the sheet visible after adding it and before trying to add any data to to ...
perhaps that's the trick?)

In any case, good catch.

 

[Alon]

Odd ... it worked fine here, even with the addition of the sheet (though I set
the sheet visible after adding it and before trying to add any data to to ...
perhaps that's the trick?)

In any case, good catch.

So after figuring that little bit out I haven't done any work on this
untill now since I got time again, and alas on solution creates
another problem. So good news, I can finally say that I have found the
root cause of all this mess.

Apparently just by adding the OLEobject, excel is never started up in
the background. So if I start doing any operations on the object
(workbook) level then the error is produced and excel force starts
itself - windows task manager helped figure this out. Any subsequent
uses don't produce this error.

The Activate method helped fix this, but the problem I had with that
is that if I want to go back to powerpoint I must use
ActiveWindow.Selection.Unselect and excel is still running in the
background after this step (which causes me a whole lot of other
problems).

Even so, I decided to go about this in a different way (since this is
but a small function in a larger program) and used the following:

dim appXLS as Excel.Application

set appXLS = new Excel.Application ' I put this in the form's
Initialize() procedure

appXLS.Quit ' I put this in the form's Terminate() procedure

This works fine up to the point of appXLS.Quit - the excel app is
still running in the background. Now its great that I got this to work
w/out producing any errors, but how can I get excel to stop eating up
my RAM and force it to quit?