2

I have the following code (using Osisoft PI SDK), which works.

Dim myPISDK As Object
Dim srv As Object
Dim pn As Object
Dim IPICalc As IPICalculation

Set myPISDK = CreateObject("PISDK.PISDK")
Set pn = CreateObject("PISDKCommon.NamedValues")

Set srv = myPISDK.Servers.Item("myPIServer")
Set IPICalc = srv

Set pn = IPICalc.ExpressionSummaries(dtI, dtF, "", TAG, 0, 0, 0, sample)

Did you noticed I am using late binding for everything (declare as object and use createObject to instanciate it) except for "IPICalc"?

That's what I need to do and I don't know how. I need to use late binding for IPICalc to.
IPICalculation is an interface from the Server object, which I get in the srv variable.

It doesn't work if I declare as object. When I do this: Set IPICalc = srv, it get the IPICalculation from the srv object. If I declare IPICalc as object, it doesn't do that.

Does anyone know if there is a way I can do this in VBA?
Among other things, I need to make it work without adding references to the VBA Project, so I need to do late binding, otherwise there will be errors since Excel won't know what is IPICalculation to declare it.

RubberDuck
  • 11,933
  • 4
  • 50
  • 95
hend
  • 565
  • 1
  • 6
  • 13
  • 3
    Why can't you just declare it as `Object`? – Rory Feb 04 '15 at 11:32
  • It doesn't work if I declare as object. When I do this: Set IPICalc = srv, it get the "IPICalculation" from the srv object. If I declare IPICalc as object, it doesn't do that. – hend Feb 04 '15 at 14:49
  • @hend What does it do instead? – GSerg Feb 04 '15 at 15:28
  • does dimming it as a Variant work? Or leaving off the "As ...." all together? – sous2817 Feb 04 '15 at 15:30
  • sous2817 it doesn't work either. GSerg: it doesn't work. It gives an error saying "The object can perform that action" or something like this. It's because without declaring IPICalc as IPICalculation type, it doesn't get the IPICalculation interface from the Server object as it should do to make the method ExpressionSummaries to work. – hend Feb 04 '15 at 16:06
  • So you have a peculiar COM object that does not expose a correct interface by default and will throw upon trying to call a method from that interface, but will satisfy an explicit `QueryInterface` call and return the correct interface? What is the exact error message? – GSerg Feb 04 '15 at 16:15
  • 1
    If that is actually the case: do add a reference in the project. If for some reason you don't want to reference the actual type library, [compile](http://stackoverflow.com/a/5196051/11683) a tiny tlb file yourself, with just `IPICalculation` interface (you have to look up its IID for that), simply deriving it from `IDispatch` without providing any methods, then reference that tlb from Excel. – GSerg Feb 04 '15 at 16:27
  • @GSerg, if I declare IPIcalc as object, the error is "Error 438 - Object doesn't accept this property or method" (sorry, it's something like this. my office is in portuguese) And you were right at your previous comment. That's exactly what happens. And your suggestion sounds good, but I have no idea how to do this. – hend Feb 04 '15 at 16:55
  • [Use `TypeLib`](http://stackoverflow.com/a/28237063/11683) to look up the Interface ID of `IPICalculation`, then refer to a MIDL manual or to [examples](http://stackoverflow.com/q/5195605/11683) to create the library, then compile it with a MIDL compiler. Alternatively download [Edanmo's olelib.tlb](http://www.mvps.org/emorcillo/en/code/vb6/index.shtml), reference that, declare the variable as `IUnknown` and explicitly call `QueryInterface` with the correct Interface ID. Why don't you want to refer to the correct tlb in the first place? – GSerg Feb 04 '15 at 17:09
  • First, I don't want to add a reference to the project. Because I have crazy users and they do the craziest things with my file, including remove my references. Also, there are users with many, many differente versios of this reference. If I add one reference and the users has other, it won't work. If I use latebinding, since the SDK works the same way even in different versions, it will work. – hend Feb 04 '15 at 17:16
  • Also, I need to paste this function in a VBS file and run it alone. And I noticed that if I write the code using late binding in Excel VBA, I just need to copy and paste it to a .vbs file and it will work. I don't know if there is a different way I could make it work in .vbs files. – hend Feb 04 '15 at 17:17
  • Could it be possible for you to call powershell scripts instead ? Osisoft has a lot of cmdlet in ps, and you coul call .net objects also ! – Madgui Mar 19 '19 at 11:01

0 Answers0