12 Replies Latest reply on Aug 29, 2015 3:53 PM by Guilherme Ferreira

    DataLink - Resize to show all values en masse

    Karl.Hodgkiss

      Is there a way to force Excel to do a "Resize to show all values" en masse?

       

      I've got a simple worksheet showing compressed data for 10 tags with the start/end time in cells A1&A2 (pretty standard stuff).

      On my first run I get 10 values for each tag, but then I change the times and now I should get 19 values in each column.... but instead I get 10 values and the resize message (CTRL+ALT+F9 doesn't help.)

      E.G.

      Number of Values: 19

      19-May-15 08:14:45      3.84795022

      19-May-15 08:29:15      1.789492369

      19-May-15 08:38:58      Shutdown

      19-May-15 08:45:58      0.37449

      19-May-15 09:47:58      4.31672

      19-May-15 11:02:28      25.93758

      19-May-15 13:30:28      85.49880

      19-May-15 14:39:28      99.19938

      19-May-15 15:42:58      96.52633

      20-May-15 07:22:37      16.99458

      Resize to show all values   Resize to show all values

       

      I found this KB article which is on the right track but the number of tags/ranges could be huge KB01035 - Recalculate a PI DataLink function without using the right-click menu

       

       

      Similar to the above, after resizing the data to display 100 values I then change it back to 10 values and save as a CSV file... but the extra 90 unused cells are also saved in the file as unwanted commas... how can this be stopped ?

      E.G.

      Number of Values:,10

      20-May-15 07:22:37,16.99458

      20-May-15 08:26:58,2.06320

      20-May-15 09:26:28,1.32765

      20-May-15 10:34:28,16.04910

      20-May-15 13:33:58,86.55754

      20-May-15 14:39:28,99.19938

      20-May-15 15:41:58,96.68435

      21-May-15 07:05:40,22.89077

      21-May-15 07:15:28,3.72859

      21-May-15 08:05:28,6.07596

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

      ,

        • Re: DataLink - Resize to show all values en masse
          Roger Palmen

          Don't know of any ways to do this en-masse without VBA. As your worksheet can contain data in the cells that cover the extension, there is no fit-for-all way to extend ranges. In the workbook design I always ensure the range covers the maximum set of rows & columns I can expect.

           

          You can use the standard array-editing in Excel to set the DataLink formula for the extended range (select full range, paste formula, CTRL-SHIFT-Enter)

          • Re: DataLink - Resize to show all values en masse
            Eugene Lee

            Hi Karl,

             

             

            You can also use Range().FormulaArray in VBA to input formulas so that "Resize to show all values" won't happen.

             

            KB01198 - Use Range().FormulaArray instead of Application.Run for DataLink VBA Programming

            1 of 1 people found this helpful
            • Re: DataLink - Resize to show all values en masse
              pthivierge

              Hello Karl,

               

              I did replied once about manually resizing an array formula:

              Re: PI Datalink - Output exceeds sheet dimension

               

              It can be worth to try.

               

              Keep us posted!

               

              Regards,

              • Re: DataLink - Resize to show all values en masse
                Karl.Hodgkiss

                Thanks all for the responses.

                Does this array formula work with something like the below example though ?

                 

                21-05-2015 14-03-17.jpg

                  • Re: DataLink - Resize to show all values en masse
                    pthivierge

                    Hi Karl,

                     

                    As long as this is an array formula it should work.

                    You have several array formulas on your spreadsheet, I count at least 8 on your sheet.

                      • Re: DataLink - Resize to show all values en masse
                        Karl.Hodgkiss

                        Hi Patrice,

                        That was just a sample of tags, there could be 100s now Excel can handle more columns, so anything that can avoid manual updates is preferable.

                          • Re: DataLink - Resize to show all values en masse
                            pthivierge

                            Hello Karl,

                             

                            In this case, if you need to automate it, I believe you will have to do some VBA code.

                            Assuming that your formulas in each columns all start at the same line:

                            • you loop all columns until you get an empty one
                            • for each N column you'll need to get the formula
                            • then you will need to call the method to refresh the array formula as shown in this KB mentioned aboveRange("a1:aN").FormulaArray = formula

                             

                            I have not tested it yet, this is a pseudo code that I believe may work.

                            I may be able to give it a try tomorrow.

                             

                            Let us know how it goes

                              • Re: DataLink - Resize to show all values en masse
                                Guilherme Ferreira

                                Helo Karl!

                                 

                                I usually do something like Patrice suggested.

                                In addition, If I don't know the number of lines my formula is going to return, I call it once with only two lines and the "show number of values" option, get the number of values returned and then run it again with the right number of lines.

                                For instance:

                                 

                                Range("a1:b2").FormulaArray = formula

                                NumValues = Cells(1,2).value

                                Range("a1:b" & NumValues).FormulaArray = formula

                                 

                                Regards

                                2 of 2 people found this helpful
                                • Re: DataLink - Resize to show all values en masse
                                  OscarGarciaLino

                                  Hi,

                                  just wondering if you guys could give me a bit of help. I am running into same issue posted here.

                                  I have tried two options:

                                  1.- Adding the reference to pidldialogs and calling .ResizeRange

                                  2.- Range("a1:b" & NumValues).FormulaArray = formula

                                   

                                  Using .resizerange get rid of the "resize to show all values" but looks to slow down my code because I have to activate the sheet and select the range. Unless you know if there is a way to use .resizerange without activating the sheet?? 

                                   

                                  Using .formulaArray my app is faster. It recalculates but I still keep getting the "resize to show all values" message.

                                   

                                  My workbook can have many columns with data, but at the end all columns will have same quantity of rows. So, I was thinking to use .resizerange for the first column and use .FormulaArray for all the rest of columns.. but didn't work.

                                   

                                  This is the piece of code I am using:

                                   

                                   

                                  Public Sub recalcResize(rUserWkSht As Worksheet)
                                      Dim addIn As COMAddIn
                                      Dim automationObject As Object
                                      
                                      Dim rUsrFirstdataCol, rUsrLastdataCol, rSecondCol As Integer
                                      Dim strFormula As String

                                    

                                          With rUserWkSht
                                          rUsrFirstdataCol = .Cells(usrFirstDataRow, usrFirstDataCol).Column
                                          rUsrLastdataCol = .Cells(usrFirstDataRow, usrLastDataCol).Column
                                         

                                           
                                          rSecondCol = rUsrFirstdataCol + 2
                                         
                                         
                                          Do While rUsrFirstdataCol <= rUsrLastdataCol
                                              If rUsrFirstdataCol < rSecondCol Then
                                                  Set addIn = Application.COMAddIns("PI DataLink")
                                                  Set automationObject = addIn.Object
                                                  Dim MyRange As Range
                                                  Set MyRange = .Cells(usrFirstDataRow, rUsrFirstdataCol)
                                                   rUserWkSht.Activate              

                                                   MyRange.Select
                                      
                                                  automationObject.ResizeRange
                                                 
                                              Else
                                                  strFormula = .Cells(usrFirstDataRow, rUsrFirstdataCol).Formula
                                                  .Cells(usrFirstDataRow, rUsrFirstdataCol).FormulaArray = strFormula
                                              End If
                                          rUsrFirstdataCol = rUsrFirstdataCol + 1
                                          Loop
                                      End With
                                  End Sub

                                   

                                   

                                   

                                   

                                   

                                   

                                    • Re: DataLink - Resize to show all values en masse
                                      Guilherme Ferreira

                                      Hi Oscar!

                                       

                                      For your second option, you need to select your whole range where data should be returned and use the "FormulaArray" like I said on my last post.

                                       

                                      In addition, If I don't know the number of lines my formula is going to return, I call it once with only two lines and the "show number of values" option, get the number of values returned and then run it again with the right number of lines.

                                      For instance:

                                       

                                      Range("a1:b2").FormulaArray = formula

                                      NumValues = Cells(1,2).value

                                      Range("a1:b" & NumValues).FormulaArray = formula

                                       

                                      If you select a single cell, your are still going to receive the "Resize" message.

                                      You should try something like:

                                      .Range(.Cells(usrFirstDataRow,rUsrFirstdataCol),.Cells(usrLastDataRow,rUsrFirstdataCol)).FormulaArray = strFormula

                                       

                                      Regards,

                            • Re: DataLink - Resize to show all values en masse
                              gachen

                              Hi Karl,

                               

                              You may find the suggestions in this thread helpful. Richard Koonce provides some helpful VBA to go through all the sheets in an Excel workbook to resize all of the arrays.

                              1 of 1 people found this helpful
                              • Re: DataLink - Resize to show all values en masse
                                Karl.Hodgkiss

                                Thanks all for the input, now I should be able to get this automated and make the end users happy