1 Reply Latest reply: Sep 19, 2014 5:05 AM by Massimiliano Brugnerotto RSS

    Get first 100records of any logical table with vbs

      Hi to all,

      how can i get the first 100 rows of any logical tables using the vb macros (not script)?

      Thanks

        • Re: Get first 100records of any logical table with vbs
          Massimiliano Brugnerotto

          Hi,

          you can create a new sheet into your Qlikview document (you can hide it) and insert a table box object (example with ID = "TB01"). Next you add all the fields of your data table to this table box object. If you don't want current selections affect data lines into the table box create a new "alterante state" into your document and connect it to the table box. To the sure to extract the first 100 rows exactly as you have load it, you can crate an automatic  row counter  (using    RowNo()  AS IDRow    into your realod script)  and order data into table box using this field.

           

          Copy and paste the following code into your VBS module :

           

          ' ***********************


          Private Function PadLeft(value, pad_text, length)

           

            ' Pad on left.

              PadLeft = Right(String(length, pad_text) & value, length)

           

          End Function

           

          ' ***********************

           

          ' Extract data from a QlikView table (formatted text lines)

          '  IDSheetObject : QlikView object ID (example : TB01)

          '  startRowIndex :  0,1,2,... Index of row where to start read lines (0=colums title, 1=first data row., 2= second data row, ...)

          '  endRowIndex   :  0=read all rows in tale,  N (>= 1) read only N rows (example 100 to read only 100 rows)

          Private Function GetTableTextData(IDSheetObject, startRowIndex)

           

            ' Var

            Dim table

            Dim colCount

            Dim rowCount

            Dim cellMatrix

            Dim rowIndex

            Dim colIndex

            Dim sRet

            Dim index

            Dim t

            Dim vbCrLf

           

            sRet = ""

            ' Initialize objects

            Set table = ActiveDocument.GetSheetObject(IDSheetObject)

            colCount = table.GetColumnCount

            rowCount = table.GetRowCount

            If rowCount <= 1 Then

            ' Error : no data to process. (rowCount=1 : only the first row is present, this row is columns titles)

            Call MsgBox("Can't extract data from QlikView table object.", 48, "Warning")

            GetTableTextData = ""

            Exit Function

            End If

            Set cellMatrix = table.GetCells2(0, 0, colCount, rowCount)

            vbCrLf = chr(10) & chr(13)

            sRet = ""

            ' Read any row (if startRowIndex=0, read also the columns title)

            index = 0

            For rowIndex = startRowIndex to rowCount - 1

            index = index + 1

               For colIndex = 0 to colCount - 1

                ' Create text line (values format is defined into table object)

               fieldValue = cellMatrix(rowIndex)(colIndex).Text

               sRet = sRet & PadLeft(fieldValue, " ", 10)

               Next

               If (endRowIndex > 0 AND index = endRowIndex) Then

                ' Read only the first endRowIndex rows...

                Exit For

               End If

               sRet = sRet & vbCrLf

            Next

            ' Data read termintaed, return data as string lines

            GetTableTextData = sRet

           

          End Function

           

          ' ***********************

           

          To use the function you can call it as the following example :

           

          ' Read first 100 lines of data plus the columns title

          txtLines = GetTableTextData("TB01", 0, 101)

           

          ' Read only first 100 lines of data (without columns title)

          txtLines = GetTableTextData("TB01", 1, 100)