4 Replies Latest reply on Dec 9, 2013 10:52 PM by ee.ashley

    Excel VBA - PIDataLink - PICompDat() - Resize to show all values

    drausim127@spiratec

      Hi,

       

      I'm using PIDataLink 2013 in combination with Excel VBA. I have developed a macro which one will be executed when a button in the Excel-Sheet is pressed. Following a short extract of this code:

       

      Function getAllValuesResult(ByVal tStartZeit As String, ByVal tEndZeit As String, ByVal tServer As String, ByVal tTagName As String) As String()
          Dim tempResult As Variant
          On Error GoTo MErr

          tempResult = Application.Run("PICompDat", tTagName, tStartZeit, tEndZeit, 9, tServer, "inside")
          
          .....
      MErr:
          MsgBox Err.Description, vbCritical, "Ausnahmefehler"
      End Function

       

      What I do is nothing more then using the Compressed Data Function of PIDataLink via VBA. The problem is that in tempResult most of the time the result is "Resize to show all values" and not the archived values.

       

      For my understanding this message only appears when the result array is to small but in this case I haven't initialized an array with a defined array size. The second strange think is when I select the checkbox "Disable Resize to show all values messge" then this message never appears and everything looks good.

       

      Hopefully somebody have an idea why this happens.

       

      best Markus

        • Re: Excel VBA - PIDataLink - PICompDat() - Resize to show all values
          hanyong

          Hi Markus,

           

          Markus Drausinger

          For my understanding this message only appears when the result array is to small but in this case I haven't initialized an array with a defined array size. The second strange think is when I select the checkbox "Disable Resize to show all values messge" then this message never appears and everything looks good.

           

          While you have not explicitly initialized an array, PI DataLink functions, especially the ones that deals with an array of cells does that when it is running. As you have guessed the message basically means that the array allocated to output the results is too small and should be resized. This happens when the function is recalculated and the new output doesn't fit into the array allocated from the previous calculation. This tends to happen for compressed data function because the number of data archived for any period can be different. One way that can workaround this is to define the function with a large timerange when you setup the page. This way, the size of array allocated to the output is big, and change the time range to a smaller one. This way subsequent query will have access to the array previously allocated. The unused cells can be left as blank, but when you highlight the cell, you will see that it is part of the function.

           

          The option that you mentioned, basically tells PI DataLink not to display the message to users to resize the results.

            • Re: Excel VBA - PIDataLink - PICompDat() - Resize to show all values
              drausim127@spiratec

              Hi Han,

               

              thank you very much for quick help. I know what you mean with array allocating but I don't know how to solve it.

               

              The strange think here is when I run my VBA code two times with the same parameter and so on, only with the difference that one time the checkbox "Show Resize to show all values message" is checked and the other time this checkbox is unchecked, I always get two different results.

               

              As you can see in my posted sample code extract I don't initialize an arrray with a fixed size. The variable which one contains the result is from type Variant (dynamic). So I don't know why the PICompDat command thinks that I have allocated an array with fixed size and this one is to small.

               

              Can you send me a short sample code of your workaround from what you have written previously?

                • Re: Excel VBA - PIDataLink - PICompDat() - Resize to show all values
                  bradym

                  Hi Markus,

                   

                  I recently wrote some similar VBA code where you specify a tag, and it would pull compressed data to a hidden sheet on button press. It automatically pastes values of interest to the first sheet. The problem I had was when the array changed sizes (new tag or more values pulled) and the macro wouldn't work.

                   

                  I am not sure if this will work with your version of Excel/PI but the command I used to recalculate the sheets and resize the arrays is:

                   

                  "Application.CalculateFull"

                   

                  I put this code directly after every compressed data pull.

                   

                  Hope this helps,

                   

                  Matt

                    • Re: Excel VBA - PIDataLink - PICompDat() - Resize to show all values
                      ee.ashley

                      I was playing around with this a bit in DataLink 2014 and I found that the Application.Run call to PICompData returns data up until the last size of an actual PICompData resize that took place on an actual spreadsheet (through the context menu in Excel).

                       

                      So, for example, if I inserted a DataLink formula normally into a sheet that resized itself to 6 rows, then if I ran some VBA code that looked like this:

                       

                       

                       
                          Dim t As Variant
                          
                          t = Application.Run("PICompDat", "sinusoidu", "*-6h", "*", 9, "\\lt0277-pi", "inside")
                          Dim height As Integer
                          Dim width As Integer
                          height = UBound(t, 1)
                          width = UBound(t, 2)
                          
                          For i = 1 To height
                              Debug.Print "[" & t(i, 1) & "]", "[" & t(i, 2) & "]"
                          Next i
                          
                          Set t = Nothing
                          
                      

                      Then the new array, t, would be populated up to the same number of rows as the DataLink function added to the spreadsheet itself (the tags do not have to match). Note that sometimes I find that the current versions of DataLink return more Excel rows in their resized array than necessary (sometimes a few extra rows).

                       

                      So here, if sinusoidu came back with 12 values, then I would see the first 5 values printed out, the 6th item would show "Resize to show all values", and the rest of the elements up to 12 would be empty.

                       

                      So if I were to hazard a guess, it seems that calling the PICompDat function manually through Application.Run may not be invoking all the same methods/calls that inserting and resizing an actual DataLink function does when done through the UI, which is affecting its ability to return the full number of results, even though the VBA array is dimensioned correctly (I tried using ReDim as well to explicitly set the array size in VBA, but it did not help).

                       

                      In older versions of DataLink, we used to be able to import the DataLink library into the VBA project and invoke some of the internal methods directly (such as the useful DLResize routine). However, I wasn't sure off hand if there was something similar we could do in DataLink 2013/2014? Perhaps there are a few methods that could be called in sequence to ensure that call to PICompDat has everything it needs to return all the necessary values?

                       

                      Edit:

                       

                      As a side note, some alternatives for getting PI data in VBA directly could be to either use ADO Recordsets with PI OLEDB Enterprise, or perhaps to use the PI SDK. With these, you will have a bit more control over the data retrieval as standard objects that are more documented.