OSIsoft: Manipulating an Excel spreadsheet embeded in ProcessBook.. v1.3

Now the previous exercise
is a fairly good way of learning about the
object models in ProcessBook. But to be
honest, a lot more customers are interested in OLE
objects in this way. They would like to insert
OLE objects like this Excel spreadsheet into
ProcessBook and then have these spreadhseets manipulated directly
by ProcessBook. For example, they would like
that every time ProcessBook opens up, to go out and recalculate this.
Now I demonstrate that, if we take a look before and after,
look at the timestamps here. I will close this
and open it back up.
Notice
very quickly the timestamps in here are going to be
changed to match the current
timestamps. And of course when that happens,
it recalculates everything on the spreadsheet.
This particular example is showing a correlation between these two
variables. Here is the code behind
that last example. As you can see,
ProcessBook itself,
the active display in ProcessBook does have an OLE
collection. Now we are kind of simplifying here,
we are saying, Let's find the first item in that collection and
let's get its object property.
I am setting its object property so that
MyObject is a variable that points to its
object property. Now that gives us access to the model
within that object. In this case,
the model is Excel, so we
use that object ActiveSheet.Calculate
to recalculate the
spreadsheet. And then we finish up by setting the
modified property equal to default.
If you do want to do some kind of
in place activation, you can also do
that. Here is an example right here of using the
activate method to do an in place activation. That's what a user
sees when he double clicks on that OLE object.
Here is a fairly minor variation
on that last example, but one I think adds a tremendous
amount of power. What I have done here
is I have setup an actual button so that
if I were to scroll back or change these,
I can go ahead and send this new information to this
particular spreadsheet that is
embedded. So for example if I were to go in here
and change this to two different tags, there is a different
tag there, and let's change this to a different tag.

I will delete that tag.
Now I can go ahead and just recalculate
and we can get a new correlation. This is a fairly strong
correlation-- .80
I can even off-set these in time, I can select one and not the other.
And then scroll backward and forward and
see if that affects the correlation.
It makes it even stronger.
Now as I am looking at this, of course I can try and figure
out... Let's make both of them scroll back
and let's see if this is something that is consistent or just an
anomaly. Now it seems like it was an anomaly at that
point because the correlation has gone off
to -.4
So the whole idea behind this is you have got a nice
little correlation machine that you
can use and you do not have to have these things correlated
in time. As you can see, if you want to, you can scroll
these back and then recalculate and see what kind of
correlations you get with different off-sets.
Now all we have done on the code side is simply
added a new command button
that simply steals all the information from the
trend using the GetTag name to get the tag name.
And it's also using
the StartTime and the EndTime
from the Trend. And it's simply sending those
over to this Excel spreadsheet
that we have got access to, again through
the object property of that OLE
object-- the Excel spreadsheet OLE object.