Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi to all,
how can i get the first 100 rows of any logical tables using the vb macros (not script)?
Thanks
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)