Bineesh

Extract AF attributes to Processbook using VBA code

Discussion created by Bineesh on Mar 26, 2012
Latest reply on Mar 26, 2012 by Bineesh

Hi,

 

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.

 

Thanks,

 

Bineesh

 

 

 

 

Outcomes