6 Replies Latest reply on Oct 14, 2016 12:08 AM by Dan Fishman

    VBA Sample code to lista PI Batch on UserForm ListView

    Allan Ferreira

      Hi,

       

      Someone have a sample code to fill na userform listview with PI batch data?

       

      I tryed to use the sample on PI SDK help but doesn´t work. I don´t retrieve data from Batch. I user PIModule, PI Batch, PI BatchList, etc.

       

      nowadays, I am using this code, but with PI API...

       

      Dim apistat As Long
      Dim valid As Long
      Dim clicks As Integer
      Dim column As Variant
      Private Sub btnAbout_Click()
          Sobre.Show
      End Sub
      Private Sub btnCancel_Click()
          Unload Batchs
          End
      End Sub
      Private Sub btnCxn_Click()
          Dim iNumber As Long
          Dim sUnit As String * 81
          Dim i As Long
          
          apistat = pilg_connectdlg(0)
           
          cmbUnit.Clear
          i = 0
          apistat = piba_getunit(sUnit, Len(sUnit), i, iNumber)
          For i = 0 To iNumber - 1
              apistat = piba_getunit(sUnit, Len(sUnit), i, iNumber)
              cmbUnit.AddItem (sUnit)
          Next i
          
          cmbUnit.ListIndex = 0
      End Sub
      Private Sub btnOK_Click()
          Dim i As Integer
          Dim box As Variant
          
          If (lstBatches.SelectedItem = "") Then
              box = MsgBox("Selecione um Rolo!", vbExclamation, "Aviso")
              Exit Sub
          Else
              ActiveSheet.Range("Rolo").Value = lstBatches.SelectedItem.Text
              ActiveSheet.Range("ProdAtual").Value = lstBatches.SelectedItem.SubItems(1)
              ActiveSheet.Range("StTime").Value = lstBatches.SelectedItem.SubItems(2)
              ActiveSheet.Range("ETime").Value = lstBatches.SelectedItem.SubItems(3)
              If chkReteste.Value Then
                  ActiveSheet.Range("Reteste").Value = 1
              Else
                  ActiveSheet.Range("Reteste").Value = 0
              End If
          End If
          
          Unload Batchs
          Application.wait (1)
          End
      End Sub
      Private Sub btnStart_Click()
          Dim BQuery As New PIBatchView
          Dim inicialdate As String
          Dim finaldate As String
          Dim i As Integer
          
          lstBatches.ListItems.Clear
          
          BQuery.QueryKind = pibvBatch
          BQuery.UnitCond = pibvLike
          BQuery.UnitTest = cmbUnit.Value
          BQuery.TimeFrom = ptoe(dtpData.Value + 1)
          BQuery.TimeTo = ptoe(dtpData.Value)
          BQuery.direction = pibvLast
          BQuery.MaxCount = 100
          BQuery.CompleteOnly = True
          BQuery.Run
          
          For i = 1 To BQuery.count
              lstBatches.ListItems.Add = BQuery.Results(i).Id
              lstBatches.ListItems(i).SubItems(1) = BQuery.Results(i).product
              lstBatches.ListItems(i).SubItems(2) = Format(BQuery.Results(i).StartTime, "dd-mmm-yy hh:mm:ss")
              lstBatches.ListItems(i).SubItems(3) = Format(BQuery.Results(i).EndTime, "dd-mmm-yy hh:mm:ss")
          Next i
          
          Set BQuery = Nothing
      End Sub
      Private Sub frmDatas_Click()
      End Sub
      Private Sub UserForm_Initialize()
          Dim iNumber As Long
          Dim sUnit As String * 81
          Dim i As Long
              
          cmbUnit.Clear
          
          i = 0
          apistat = piba_getunit(sUnit, Len(sUnit), i, iNumber)
          For i = 0 To iNumber - 1
              apistat = piba_getunit(sUnit, Len(sUnit), i, iNumber)
              cmbUnit.AddItem (sUnit)
          Next i
          
          If apistat = 0 Then cmbUnit.ListIndex = 1
          dtpData.Value = DateValue(Now)
          lstBatches.ListItems.Clear
          clicks = 0
      End Sub
      Private Sub lstBatches_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
          Dim i As Integer
            
          lstBatches.Sorted = True
          
          If (column = ColumnHeader) And (clicks Mod 2 = 0) Then
              lstBatches.SortOrder = lvwAscending
              clicks = clicks + 1
          ElseIf (column = ColumnHeader) And (clicks Mod 2 = 1) Then
              lstBatches.SortOrder = lvwDescending
              clicks = clicks + 1
          Else
              lstBatches.SortOrder = lvwAscending
              clicks = 1
          End If
          lstBatches.SortKey = ColumnHeader.index - 1
          lstBatches.Refresh
          lstBatches.Sorted = False
          column = ColumnHeader
      End Sub
      Private Sub UserForm_Terminate()
      CalculoAutomatico
      End Sub
      

      I want to replace this code... Any tip?

       

      Thanks in advance.

        • Re: VBA Sample code to lista PI Batch on UserForm ListView
          kduffy

          Hi Allan,

           

          Could you provide the Excel sheet that you're working with, or a screenshot of the buttons and listboxes? I'm not familiar with programming a listbox so I'm having a tough time visualizing what this is doing.

           

          As for replacing the PI API commands, at first glance it looks like what you're trying to do can be done with the help of the PI BatchView Programming Help file

          %pihome%\help\PIBatchViewProgramming.chm

           

          Those functions are separate from the standard PISDK functions.

           

          Let me know if you would like assistance finding the correct functions to use.

           

          Kelsey

            • Re: VBA Sample code to lista PI Batch on UserForm ListView
              Allan Ferreira

              Hi Kelsey,

               

              I also don´t have much experience with programming.

               

              To use the code above, I need to create a trust for every computer that runs this worksheet due to use PI API, otherwise the units names are not displayed.

               

              batch.png

                • Re: VBA Sample code to lista PI Batch on UserForm ListView
                  kduffy

                  Thanks for the screenshot Allan,

                   

                  This look fairly similar to the out-of-the-box BatchView search that outputs batch data into excel.

                   

                  Could you explain what you're doing with the batch searching and the batches that BatchView can't already do? Hopefully we can get to a solution that doesn't involve custom coding at all.

                   

                  Kelsey

                    • Re: VBA Sample code to lista PI Batch on UserForm ListView
                      Allan Ferreira

                      Hi Kelsey,

                       

                       

                      In this case, I need to select only one batch in the list, and send to Excel to provide starttime and endtime info to send data to PI Server through PI SDK (PI PUTval)

                       

                       

                      The code above is old, and needs that the computer be listed as trust in PI Server. without it does not work.

                       

                       

                      About standard batchview search box, it doesn´t allow select only one batch listed. All the batches listed are send to Excel.

                        • Re: VBA Sample code to lista PI Batch on UserForm ListView
                          kduffy

                          Hi Allan,

                           

                          I'll try to work on a script for you that should work, but in the meantime, the code you will want to use is found within this section of the help guide (%pihome%\help\PIBatchViewProgramming.chm):

                           

                          Then within the SearchCriteria class, you can using the BatchItemCriteria Object to set things like the unit name:

                          You can have your search box control the unit name, which can be set within the SearchCriteria object using something like this line:

                           

                          Dim tempCrit As SearchCriteria

                           

                          tempCrit .BatchItemCriteria(pibvUnitBatch).AttributeMasksStrByType(pibvUAttrUnitName) = <Drop down list of units>.<Selected value>

                          tempCrit .TimeFrom = <Date Time control named Data Inicial>.<value>

                           

                          The above shows how to reference the SearchCriteria level and the BatchItemCriteria level.

                           

                          Then you can execute the search using the SearchQuery.Search(), and go through the results using the SearchQuery.Results, which would populate the user form with the batches.

                           

                          From there, the user form would take over again and you can program it to select one of the batches and put that data into Excel in specific.

                           

                          Like I said, I'll try to throw something together, but this should get you pointed in the right direction for now.

                           

                          Kelsey

                          • Re: VBA Sample code to lista PI Batch on UserForm ListView
                            Dan Fishman

                            Take a look at this post: https://pisquare.osisoft.com/message/60331#comment-60331

                             

                            It should be very easy to do a batch search.  You really just in a PI Server object, and the batchdatabase object to do a batch search.  Note, if you are trying to use the PI API and using the PI Batch Database you won't have much luck.

                             

                            Dan