2 Replies Latest reply on Mar 26, 2012 10:54 AM by Bineesh

    Extract AF attributes to Processbook using VBA code




      Is there a method (might be using PI OLEDB Enterprise) that I can extract AF attribute values to a Processbook using VBA.


      For Excel reports, I have used the following VBA code to extarct AF data:




      With ActiveSheet.QueryTables.Add(Connection:=Array( _
             "OLEDB;Provider=PIOLEDBENT.1;User ID="""";Initial Catalog=<<DB NAME>>;Data Source=<<AF SERVER>>;Extended Properties="""";Integrated Security=" _
              , _
              "SSPI;Command Timeout=-1;Log Level=0;Log File="""";Shorten Primary Keys=False;Always Return Rowset=False;Defer Execution=False;Disa" _
              , _
              "ble Server Selection=False;Time as Double=False;Optimization Log Limit=100;Keep Default Ordering=False;Function Errors as NULL=F" _
              , _
              "alse;Cancel On Low Resources=True;Show Hidden Metadata=False;Allow Expensive=False;Use Agent=True;Initial Catalog=<<DB NAME>>" _
              ), Destination:=Range("K1"))
              .CommandType = xlCmdSql
              .CommandText = Array( _
              "SELECT eh1.Path, eh1.Name as ""Plant"", a1.Value as ""equipmentID"" FROM [<<DB NAME>>].[Data].[Archive] a1 " & Chr(10) & "INNER JOIN [<<DB NAME>>].[Asset].[ElementAttribute] ea1 ON a1.ElementAttributeID = ea1.ID" & Chr(10) & "INNER JOIN [<<DB NAME>>].[Asse" _
              , _
              "t].[ElementHierarchy] eh1 ON ea1.ElementID = eh1.ElementID" & Chr(10) & "WHERE ea1.Name='equipmentID'" & Chr(10) & "ORDER BY a1.Time" & Chr(10) & "OPTION(ALLOW EXPENSIVE)" _
              .FieldNames = True
              .RowNumbers = False
              .FillAdjacentFormulas = False
              .PreserveFormatting = True
              .RefreshOnFileOpen = False
              .BackgroundQuery = True
              .RefreshStyle = xlInsertDeleteCells
              .SavePassword = False
              .SaveData = True
              .AdjustColumnWidth = True
              .RefreshPeriod = 0
              .PreserveColumnInfo = True
              '.SourceConnectionFile = _
          End With




      Similarly do we have a method in Processbook to extact AF attributes, probably using PI OLEDB Enterprise Library.


      Please suggest.


      Appreciate if you can share some code snippets.









        • Re: Extract AF attributes to Processbook using VBA code

          Yes, you can access data from AF via PI OLEDB Enterprise through Microsoft ADO library. Here's an example:

          Sub GetDataViaPIOLEDBEnt()
              Dim conn As ADODB.Connection
              Dim cmd As ADODB.Command
              Dim query As String
              Dim rs As ADODB.Recordset
              Dim output As String
              Set conn = New ADODB.Connection
              conn.ConnectionString = "Provider=PIOLEDBENT.1;Data Source=hyong;Integrated Security=SSPI;"
              query = "SELECT Name FROM [AFDB].[Asset].[Element] WHERE DBReferenceTypeID IS NOT NULL"

              Set cmd = New ADODB.Command
              cmd.ActiveConnection = conn
              cmd.CommandText = query
              Set rs = cmd.Execute
              While Not rs.EOF
                  output = output + ";" + rs.Fields(0).Value
              ThisDisplay.Text1.Contents = output
          End Sub

          The example basically executes the query using PI OLEDB Enterprise, concat the output into a single string and display it on an existing Text symbol called "Text1". you can modify the query according to how you would like the output from the query to be displayed.


          Note: you do have to add reference to the Microsoft ActiveX Data Objects Library for this to work. 

          1 of 1 people found this helpful