I am working on Excel VBA and I want to have a button that when clicked, inserts a PI trend in the sheet.
Is that possible?
There are two ways of doing this, please find the excel sheet with the VBA code and an example of it. (Find the Excel Workbook attached).
First Option, using Excel functions. This is just an example for you to know that Excel gives you the option of creating different graphs and charts with VBA, you could make this as complex as you need in order to fulfill your goal.
The VBA Coding Guide For Excel Charts & Graphs — The Spreadsheet Guru
You can find in in the following link more information here about the chart object that I will be using in my VBA code.Chart Object (Excel)
Sub CreateChart()'PURPOSE: Create a chart (chart dimensions are required)
Dim rng As RangeDim cht As ChartObject
'Your data range for the chart Set rng = ActiveSheet.Range("A4:B28")
'Create a chart Set cht = ActiveSheet.ChartObjects.Add( _ Left:=ActiveCell.Left, _ Width:=450, _ Top:=ActiveCell.Top, _ Height:=250)
'Give chart some data cht.Chart.SetSourceData Source:=rng
'Determine the chart type cht.Chart.ChartType = xlXYScatterLines End Sub
Second option, using legacy features from PI Datalink. (sheet1 from the workbook)
NOTE: As mentioned in the previous entry of the thread, this is all legacy. This VBA project needs .dlls that will not be install by default in the actual version (v2016) and in future releases of PI Datalink.
In order to refresh the trend you only need to press on the "Draw Graph" button. You could modify the code as you wish in order to fulfill your needs.
Private Sub CommandButton1_Click() Dim i As Long Dim myTrendConfig As TrendConfig ' Make sure the trend is initialized correctly. ' This is useful when opening the excel file and clicking before the ' trend has time to initialize. ExcelTrendWizard1_Sheet1.Timer_Connect ' Remove the existing trace. Since there is only 1, it is item 1
' Delete existing traces ExcelTrendWizard1_Sheet1.ExcelRemoveQuerySpec (1)
' Add the new trace ExcelTrendWizard1_Sheet1.ExcelAddQuerySpecs "$B$20:$C$41", 1 ' Update Title & Use single scale Set myTrendConfig = PITrend1_Sheet1.GetConfiguration myTrendConfig.Title = "Using 10 Rows - Single Scale" myTrendConfig.VerticalAxis.VerticalScales.ScaleConfiguration = pbwVScaleSingleScale ' Update trend configuration PITrend1_Sheet1.SetConfiguration myTrendConfig PITrend1_Sheet1.HorizontalAxis.SetScrollbarVisible True, True ' Force a trend revert to update the trace PITrend1_Sheet1.Revert ' Now force the time range to a 10 min step PIExcelData1_Sheet1.SetTimeRange True, "09-Nov-2016", "10-Nov-2016" End Sub
Private Sub PITrend1_Sheet1_OnTimeRangeChange(ByVal Source As PBWebClient.pbwTimeRangeChangeSourceEnum, ByVal Temporary As Boolean, ByVal StartTime As Variant, ByVal EndTime As Variant, ByVal TimeZoneInfo As Variant)
Please let us know if this is helpful for you,
PI DataLink no longer includes trends, so you'd have to embed a ProcessBook object in the Excel sheet if you wanted a PI trend there.
Alternately, you could bring in the PI data into a separate sheet (using DataLink functions or PI calls from VBA) and use a standard Excel trend object to trend the data.
Which version of PI Datalink are you using?
In the latest version PI Datalink 2015 (188.8.131.52) there is the possibility of adding the "Insert Trend" option of PI Datalink. This is included in the PI Datalink (legacy) add-in.
In order to enable it please do the following:
Let me know if this works for you.
Did you find the answer on how to insert a trend using Excel VBA coding ?
Retrieving data ...