2 Replies Latest reply on Oct 27, 2018 3:10 PM by Dan Fishman

    Automatically Populating information


      Hello, I am curious if I can make this excel add on simulate the same function that I am currently using on another system.

      This is what I have set up so far on my other system. I am using RSLINX classic with a topic and each column has a separate tag. Once my "trigger" ( splice ) happens this information will populate underneath the existing row to show the next sets of data for that event. I am putting this report together so that our quality team will be able to track the parameters that have been set for each roll of paper we make. I could use the existing sheet I have created but I would have to leave my computer on site all the time and it will slow it down tremendously. We have another computer that has the factorytalk view historian on it which then sends the data to the PI add on in excel. I would like to set up a sheet similar to the one I have created already except I would like to use the PI system to do it instead of using RSLINX on my computer. I am not sure how to get started. I have never used the PI software and I am having trouble explaining myself to the tech support when I call.


      Once the trigger happens the sheet will continue to add another row of data like this.

      And continue and continue.


      Is there any possibility I can do something like this with the PI system?


      This is the VBA code I am using for my current sheet.



      Private Sub Worksheet_calculate()


      On Error GoTo error

      Set keycell = Range("c2")
      If keycell.Value = 1 Then
      RSIchan = DDEInitiate("rslinx", "Ahlstrom_PT968")
      RSIchan1 = DDEInitiate("rslinx", "Scienta_1")
      RSIchan2 = DDEInitiate("rslinx", "Scienta_2")
      RSIchan3 = DDEInitiate("rslinx", "RTO")
      Dim lngrow As Long
      DDEPoke RSIchan, "J_Test.0", Range("D2")

      lngrow = 4

      For lngrow = lngrow To 500
      If Cells(lngrow, 2) = "" Then Exit For

      Data = DDERequest(RSIchan, "MMI_Real[99]") 'Line speed FPM
      Data1 = DDERequest(RSIchan, "MMI_Real[34]") 'Metering gap 1 drive side
      Data2 = DDERequest(RSIchan, "MMI_Real[35]") 'Metering gap 1 operator side
      Data3 = DDERequest(RSIchan, "MMI_Real[65]") 'Applicator gap drive side
      Data4 = DDERequest(RSIchan, "MMI_Real[66]") 'Applicator gap operator side
      Data5 = DDERequest(RSIchan, "MMI_Real[67]") 'Metering gap 2 drive side
      Data6 = DDERequest(RSIchan, "MMI_Real[68]") 'Metering gap 2 operator side
      Data7 = DDERequest(RSIchan, "MMI_Real[8]") 'Dryer 1 Tension
      Data8 = DDERequest(RSIchan, "MMI_Real[87]") 'Dryer 2 Tension
      Data9 = DDERequest(RSIchan, "MMI_Write[12]") 'Unwinder Tension SP
      Data10 = DDERequest(RSIchan, "MMI_Real[27]") 'Unwinder Tension PV
      Data11 = DDERequest(RSIchan, "MMI_Real_56[76]") 'Rewinder Tension SP
      Data12 = DDERequest(RSIchan, "MMI_Real_56[66]") 'Rewinder Tension PV
      Data13 = DDERequest(RSIchan, "InletBowRoll_DF.DrwRead") 'Inlet Bow Roll Draw SP
      Data14 = DDERequest(RSIchan, "MetRoll1_DF.DrwRead") 'Metering Roll 1 Draw SP
      Data15 = DDERequest(RSIchan, "AppRoll1_DF.DrwRead") 'Applicator Roll 1 Draw SP
      Data16 = DDERequest(RSIchan, "AppRoll2_DF.DrwRead") 'Applicator Roll 2 Draw SP
      Data17 = DDERequest(RSIchan, "MetRoll2_DF.DrwRead") 'Metering Roll 2 Draw SP
      Data18 = DDERequest(RSIchan, "PenPathTop_DF.DrwRead") 'Top Penetration Path Roll Draw SP
      Data19 = DDERequest(RSIchan, "PenPathBot_DF.DrwRead") 'Bottom Penetration Path Roll Draw SP
      Data20 = DDERequest(RSIchan, "CorrRoll_1_DF.DrwRead") 'Corrugator Bottom Roll Draw SP
      Data21 = DDERequest(RSIchan, "MMI_Real[36]") 'Corrugator Gap Drive Side
      Data22 = DDERequest(RSIchan, "MMI_Real[37]") 'Corrugator Gap Operator Side
      Data23 = DDERequest(RSIchan, "MMI_Real_56[81]") 'Current Rewinder Footage Count
      Data24 = DDERequest(RSIchan, "MMI_Real_56[83]") 'Previous Rewinder Footage Count
      Data25 = DDERequest(RSIchan, "D1Z1.Setpoint")
      Data26 = DDERequest(RSIchan, "D1Z1.TempFFbk")
      Data27 = DDERequest(RSIchan, "D1Z2.Setpoint")
      Data28 = DDERequest(RSIchan, "D1Z2.TempFFbk")
      Data29 = DDERequest(RSIchan, "D1Z3.Setpoint")
      Data30 = DDERequest(RSIchan, "D1Z3.TempFFbk")
      Data31 = DDERequest(RSIchan, "D1Z4.Setpoint")
      Data32 = DDERequest(RSIchan, "D1Z4.TempFFbk")
      Data33 = DDERequest(RSIchan, "D1Z5.Setpoint")
      Data34 = DDERequest(RSIchan, "D1Z5.TempFFbk")
      Data35 = DDERequest(RSIchan, "D1Z6.Setpoint")
      Data36 = DDERequest(RSIchan, "D1Z6.TempFFbk")
      Data37 = DDERequest(RSIchan, "D2Z7.Setpoint")
      Data38 = DDERequest(RSIchan, "D2Z7.TempFFbk")
      Data39 = DDERequest(RSIchan, "D2Z8.Setpoint")
      Data40 = DDERequest(RSIchan, "D2Z8.TempFFbk")
      Data41 = DDERequest(RSIchan, "D2Z9.Setpoint")
      Data42 = DDERequest(RSIchan, "D2Z9.TempFFbk")
      Data43 = DDERequest(RSIchan, "D2Z10.Setpoint")
      Data44 = DDERequest(RSIchan, "D2Z10.TempFFbk")
      Data45 = DDERequest(RSIchan1, "Scanner1_BoneDry") 'Raw bone dry 1
      Data46 = DDERequest(RSIchan2, "head2.mean") 'raw bone dry 2
      Data47 = DDERequest(RSIchan2, "Scanner2_BoneDry") 'saturated bone dry 1
      Data48 = DDERequest(RSIchan2, "Resin_Weight_BD") 'saturated bone dry 2
      Data49 = DDERequest(RSIchan3, "FIC_43_1_SCP") 'LEL SP for RTO 1
      Data50 = Now()

      Cells(lngrow, 2).Value = Data
      Cells(lngrow, 3).Value = Data1
      Cells(lngrow, 4).Value = Data2
      Cells(lngrow, 5).Value = Data3
      Cells(lngrow, 6).Value = Data4
      Cells(lngrow, 7).Value = Data5
      Cells(lngrow, 8).Value = Data6
      Cells(lngrow, 9).Value = Data7
      Cells(lngrow, 10).Value = Data8
      Cells(lngrow, 11).Value = Data9
      Cells(lngrow, 12).Value = Data10
      Cells(lngrow, 13).Value = Data11
      Cells(lngrow, 14).Value = Data12
      Cells(lngrow, 15).Value = Data13
      Cells(lngrow, 16).Value = Data14
      Cells(lngrow, 17).Value = Data15
      Cells(lngrow, 18).Value = Data16
      Cells(lngrow, 19).Value = Data17
      Cells(lngrow, 20).Value = Data18
      Cells(lngrow, 21).Value = Data19
      Cells(lngrow, 22).Value = Data20
      Cells(lngrow, 23).Value = Data21
      Cells(lngrow, 24).Value = Data22
      Cells(lngrow, 25).Value = Data23
      Cells(lngrow, 26).Value = Data24
      Cells(lngrow, 27).Value = Data25
      Cells(lngrow, 28).Value = Data26
      Cells(lngrow, 29).Value = Data27
      Cells(lngrow, 30).Value = Data28
      Cells(lngrow, 31).Value = Data29
      Cells(lngrow, 32).Value = Data30
      Cells(lngrow, 33).Value = Data31
      Cells(lngrow, 34).Value = Data32
      Cells(lngrow, 35).Value = Data33
      Cells(lngrow, 36).Value = Data34
      Cells(lngrow, 37).Value = Data35
      Cells(lngrow, 38).Value = Data36
      Cells(lngrow, 39).Value = Data37
      Cells(lngrow, 40).Value = Data38
      Cells(lngrow, 41).Value = Data39
      Cells(lngrow, 42).Value = Data40
      Cells(lngrow, 43).Value = Data41
      Cells(lngrow, 44).Value = Data42
      Cells(lngrow, 45).Value = Data43
      Cells(lngrow, 46).Value = Data44
      Cells(lngrow, 47).Value = Data45
      Cells(lngrow, 48).Value = Data46
      Cells(lngrow, 49).Value = Data47
      Cells(lngrow, 50).Value = Data48
      Cells(lngrow, 51).Value = Data49
      Cells(lngrow, 52).Value = Data50



      DDETerminate RSIchan

      End If

      End Sub






      Thank you for any help with this!

        • Re: Automatically Populating information
          Dan Fishman

          You can do with using PI Datalink with little to no VBA.  You might have to use VBA to automatically resize the arrays.


          This all really depends on how the events are triggered.  I would recommend using a PI Tag to act as the trigger and bring in this compressed data with from T to * (today to now).  Make sure Datalink updates are on (enable the automatic Datalink updates for full calculation) such that as the data arrives the spreadsheet automatically is updated.  You will face issues with the resize array function but there are a few KB articles on resolving it via VBA or other tricks depending on how users interact with the sheet.


          Once you have the trigger in, I would then recommend using timed filtered Datalink function to bring in all of the row values each time the trigger updates.  You will have to select the time range of your compressed values and then some more.  Other options include putting on filters to remove any datalink warning messages.  I am surprised that techsupport was unable to help since this seems like a reasonable request.  I might be missing something.  If I get a chance, I can post a sample spreadsheet tomorrow.  A travel day, so time is tight..