Greg, ProcessBook uses plotvalues. Plotvalues take in account the resolution you can display and retrieves a corresponding amount of data (but not more than compressed data). But you can not influence what ProcessBook uses as the resolution. Also, the returned amount of data depends on the compressed data.
How about passing only the tag names to Excel and using Datalinks sampled data for retrieving the values from PI?
Is that something that could be done with a single button press by a user from within ProcessBook? I imagine I'd have to figure some way to trigger the macro within Excel so that it can execute the commands to run the datalink queries and resize the arrays depending on the sample interval and number of tags selected in ProcessBook.
I'm a decent VBA programmer, but not a professional (I do DeltaV, AB PLC's and other process control work, so computer science is not my full time job) so this might be getting a bit beyond my capabilities...but any tips you have would be helpful...
first of all we need to add MS Excel to PB VBA. In the VBA editor, Tools->References and add "Microsoft Excel 12.0 Object Library".
Next step is from our VBA code to start MS Excel:
Dim xlTmp As Excel.Application Set xlTmp = New Excel.Application xlTmp.Visible = True
Afterwards we create a new empty workbook and put in the A1 cell the tagname (in my case SINUSOID - you will have to catch that from your trend):
Dim xlWrk As Excel.Workbook Set xlWrk = xlTmp.Workbooks.Add() Dim xlSht As Excel.Sheets Set xlSht = xlWrk.Sheets xlSht(1).Cells(1, 1).Value = "SINUSOID"
It turns out that Excel does not like add ins when started from VBA (see here ) - so we have to do some magic:
Dim addXL As Excel.AddIn Set addXL = xlTmp.Addins.Add(Environ("PIHOME") + "\Excel\PIPC32.XLL") addXL.Installed = False addXL.Installed = True xlTmp.ActiveWorkbook.RefreshAll
The following constructs the formula for PI DataLink. In my case sampled data for the last day in 1h steps. Note that you have to adjust the PI Servername (SCHREMMERAVMPI) for your environment:
Dim tmpEquation As String tmpEquation = "=PISampDat(A1," _ & Chr(34) & "Y" & Chr(34) & ", " _ & Chr(34) & "T" & Chr(34) & ", " _ & Chr(34) & "1h" & Chr(34) & ",1," _ & Chr(34) & "SCHREMMERAVMPI" & Chr(34) & ")"
What is left is adding this as a FormulaArray and adjusting the format of the timestamp. We start in A2 and expect to get 24 pairs of timestamp & value, so the range ends at B26:
xlSht(1).Range("A2:B26").FormulaArray = tmpEquation xlSht(1).Range("A2:A26").NumberFormat = "dd-mmm-yy HH:mm:ss"
hope this helps!
vcampus_excel_from_button.pdi 33.0 KB
That will be a big help.
Thanks a bunch,