OSIsoft: Insert a PI OLE Object into Excel . v1.3

Uploaded by OSIsoftLearning on 20.09.2011


In this last example we do
something completely different than before. In this case
we are not going to be building an external ProcessBook Display,
we are going to be adding an OLE Object. Now, that's different than
a, a PI Display. An OLE Object is an
embedded object that we can put inside Excel
that represents a PI Display. I will demonstrate. I will go
ahead and click on this. What we are seeing here is an OLE Object.
This is a PI Display-type OLE Object that's been
inserted directly into Excel. Excel is an
OLE Object Container. And, if I select
another tag, and choose another, I can just keep on adding
Trends to this OLE Object. Now, the
beauty of this, of course, is that it does not open up a separate
window. It does have ProcessBook running in the background, but
it does not open up a second window. And a user can always
double-click on this to do an in-place activation. That's
the in-place activation of that ProcessBook Object.
He can now scroll around or zoom in and out.
I will go back into Excel.
So that's an example. The -- there are some
brand new things that we need to over, to look at how this
has been done. And these are not completely obvious.
First of all, there is something called the OLE
Objects Collection in Excel.
Most OLE Object Containers
have an OLE Object Collection. ProcessBook has one as well.
So this OLE Objects Collection is what we use
to get access to these OLE Objects.
Each OLE Object has a ProgID.
We are going to check to see if the ProgID is
a PI Display-type ProgID, and if it's not, we are going to
add an OLE Object of the correct type.
Finally, every OLE Object
has itself an Object Property, and that's
where we get access to the Object Model of the Parent
Program. In this case, ProcessBook's Object Model.
We can get to that through the OLE Object's Object
Property. Let's go ahead and take a look at the code
that's going on behind here. First of all, let me select
a location. That's a tag and that's also going to be the location
of the OLE Object, so I will go ahead and click on this Display,
an OLE Object, and I have got a breakpoint that should stop it just
at this point. See, I am doing a For Each here.
Object X has been defined
as an Excel OLE Object.
And we are going to go through the OLE Objects
Collection in this current worksheet. This
worksheet has an OLE Objects Collection. We are going to
look for an OLE Object that has the ProgID
equal to PI Display Type.
And if we do find one, we are going to go ahead
and say Yes, we have already got an Object in there.
Now we are doing this because, if we do not find one,
we are going to want to set this variable equal to False, and in that
case, we are going to add our own. So, in the one case, we are
going to use the existing. In the other case, we are going to, we are going to add.
Now, in this case, there are no OLE Objects
of that type, so if I keep executing
this, you will notice the first ProgID.
No, that's not it. That's a Command Button.
So, we are saying this is False.
The next was another Command Button. The one
after that was another Command Button. So, all we are getting here is
Command Buttons, and eventually we will get
to the end of that list. Yeah. So, we are in the end of the
list of OLE Objects.
So, at this point, we have determined that OLE Object
Exists is equal to False. So, what we need to do
is we need to add a PI Display-type,
OLE Object, to this Excel Worksheet.
Now we will look at the Before and After. As you can see,
there's no OLE Object there right now, but if
I execute this line of code, that should go ahead and add
that OLE Object. It's taking a little bit of time
to do that. And
there it is. So, you can see it added that and
continued on with the execution, and
put the Trend of the Object that I had selected on there.
Let's see what would have happened if there had
been, already, an OLE Object in here
of PI Display-type. See, what would have happened is,
very first thing we would have found -- well, the very first
OLE Object that has this type -- we would have -- at that
point Object X would have been a pointer
to that OLE Object, and at that point we simply
say that this is True and we exit out of the for. When
we exit out of the for, that means this,
this right here, is still the Object that we --
it's still a pointer to the Object that we want.
So that means later on, when we get to this If-Then statement, of
course this is True, so we do not set
that Object X again. We do not have to create a new one.
Now, once we have Object X --
Object X is our way of getting access
to that OLE Object that is the right type.
Once we have that, we need to get to one more thing.
That is the Object Property of the,
of that OLE Object. That's an obscure
concept, but every OLE Object has itself an
Object Property which is our entryway into
the Object Model of the -- of whatever
Object that represents. In this case, a ProcessBook.
This is going to be a ProcessBook Display.
I would like to
clear up something about this Object right here. If you
remember, myobj -- that's what we used here. We set
the Object Property of that OLE Object.
We set a pointer in myobj to that
Object Property. That variable is actually
defined as an Object Variable. It's not a PI
Display-type Variable or any other type of variable. See, it
is through the Object Property that we get
access to the Automation Interface, and through the Automation Interface
we get the appropriate Object Model.
So that simply has to be an Object.
And, as you can see, what we have done here is the same thing
we have done before when we -- once we have
this Object Model, myobj,
we can drill down through it by saying
let's, let's assign a Trend to be
equal to the result of the Add Method on the Symbols Collection
of that myobj variable.
That will get access to the Display
within that OLE Object and
the Symbols Collection within that Display, and this will add
a brand new Trend. And then, after that, we simply do the
Add Trace like we have done before.