4 Replies Latest reply on Apr 1, 2015 4:34 PM by bshang

    VBA code to retrieve AF Elements

    helijgr

      Hi guys,

       

      I am developing an Excel application which I need to retrieve some elements of PI AF.

      To let clear: There is an Enterprise, which has some Units, compound by Areas, that has Variables, Control Loops and Equipments.

      In the application, I want to retrieve, for example, all Variables of type Current of Area. Variables, Current and Area are going to be selected by the user of the application

      (those values will be in some cell).

      Does someone know a VBA code that allows me to do that?

        • Re: VBA code to retrieve AF Elements
          bshang

          Hi Heli,

           

          Access to AF is primarily through .NET languages (C# and VB.NET) via AF SDK, so it will be much more work with VBA (COM). You will need to create a COM-callable wrapper. An example project is here AF Wrapper for Processbook

           

          Although it is mentioned for ProcessBook VBA, it is relevant for Excel VBA too. Although it's possible to go down this path, it's not the first one I would recommend.

           

          Will this application primarily be for displaying or querying data? I'm wondering if Datalink will meet your requirements then. You can design your AF structure based on the model above and allow Datalink UI's to perform the searching/querying. It may not meet 100% requirements but uses an off-the-shelf product. Datalink also supports simple dropdown selections for ad-hoc retrieval.

           

          Are you developing an Excel Add-in or embedded VBA code within a workbook? If it's pure VBA, then the COM-callable wrapper will need to be used.

           

          Is there a strict requirement to use Excel? If not, a desktop app using .NET AF SDK and grid controls might be more elegant option.

            • Re: VBA code to retrieve AF Elements
              helijgr

              Thank you for the answer Barry,

               

              Excel is a contractor's requirement, this is the reason that I have to develop the solution in a embedded vba code within a workbook.

              In my primary app, I am going to construct kind of analog's report. The user will select the area and the type of analog (current, flow, density) and I have to show the sampled data (for one day long, at each hour of day), minimum, average and maximum at the day and maybe another useful information. So, in another worsheet I intend to take the user's selection and retrieve data for AF (get the elements that has the appropriate type and belongs to the area). Then, construct automatically the queries using Datalink and finally show the results and the first worksheet (where the user's has selected the type and area).

                • Re: VBA code to retrieve AF Elements
                  bshang

                  Hi Heli,

                   

                  You may be better off using Datalink exclusively for this use case. Datalink 2014 introduced an Asset Filter search, allowing you to search by Element name, Element template, Element category, Attribute value, etc. The search will return attribute paths which can then be used in Sampled Data, min, max, etc. However, I also understand specific requirements and limiting the scope of the custom application.

                   

                  I think there are two options here:

                  1) Use Datalink for the data retrieval methods, but write custom VBA code to build the attribute paths from the user query. The attribute paths are then referenced by Datalink. The benefit here is that an AF COM-wrapper might be avoided if you can import the AF hierarchy or expose it via another interface, such as PI OLEDB Enterprise as Patrice suggests below. I would suggest looking into the PI OLEDB Enterprise route that Patrice suggests below, although your customer will need access to this product. The user's queries will be passed in as SQL queries to OLEDB, and attribute paths can be returned and later referenced by Datalink. You can also perform the data retrieval entirely in PI OLEDB Enterprise but Datalink may be more optimized for certain retrievals.

                   

                  2). Use AF COM-wrapper to build the entire VBA application. This is the least recommended approach. For every method you want to add in VBA, you'll need to add it in the COM-wrapper class, build, register, and test it, so there's a lot of extra work.

                   

                   

                   

                   

                  hackathon_sig.png

                  Click here to register for the TechCon Programming and Security hackathon on April 29, 2015!

              • Re: VBA code to retrieve AF Elements
                pthivierge

                Hello Heli,

                 

                In addition to what Barry proposes, you may also consider using PI OLEDB enterprise to query the AF Database in VBA.

                You can get some examples in this post: Extract AF attributes to Processbook using VBA code