Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
Long story short: I need to write a macro that can look up columns in a straight table / table box, based on the field name.
It all works perfectly well, with one little snag: the macro call oCol.Name fails if the column is an expression, or a calculated dimension. Even if I set a label.
Any ideas to fix or get around this, please?
Function ReadChartColumns() Dim oCol, oTable, sChartId sChartId = "CH01" MsgBox "DBG: Getting columna names from '" & sChartId & "'" Set oTable = ActiveDocument.GetSheetObject(sChartId) MsgBox "DBG: Reading " & oTable.GetColumnCount & " columns from '" & sChartId & "'" For iCol = 0 To oTable.GetColumnCount-1 'MsgBox "DBG: Getting column: " & iCol Set oCol = oTable.GetField(iCol) MsgBox "DBG: Got column: " & iCol sField = oCol.Name ' This line fails MsgBox "DBG: Got column " & iCol & " field name: " & sField Next End Function
Cheers,
Vegard
All columns are not connected to fields so it is dangerous to assume that the GetField() returns a field object.
Try using this snippet in your function to see what you get:
Set stp = oTable.GetProperties
Set exps = stp.Expressions
Set expr = exps.Item(0)(0).Data.ExpressionData
MsgBox expr.Definition.v
This will return both expressions but also field names .
The object model of QlikView is quite hierarchical and can seem a bit convoluted .....
This is definitely the right answer to my question. Unfortunately, I didn't tell you what I need the column name for.
I need to export the some of the columns in a straight table in a peculiar way. My idea was to use the column name as identification/lookup. (I could use column numbers, but it is difficult to read, difficult to maintain, and adding new columns would possibly break the export.)
Therefore I looking for a way to iterate:
'Column name / label / tag' combined with 'Row number', which could give me the 'Cell value'.
I've search through the Qlik's API Guide. But I haven't found a link to the column number, or another way of identifying a cell, from the IArrayOfArrayOfMainExpressionData, IMainExpressionData, IChartDimensionData in the solution above. Or any other classes, for that matter.
However, the API has many members, and I might have missed an easy solution.
Does anyone see a solution to my problem?
Best regards,
Vegard
You could read the chart cell for cell within a loop including the header and check them in an if-loop, see here an example from APIGuide.qvw:
set table = ActiveDocument.GetSheetObject( "CH01" )
w = table.GetColumnCount
h = table.GetRowCount
set CellMatrix = table.GetCells2(0,0,w,h)
for RowIter=0 to h-1
for ColIter=0 to w-1
msgbox(CellMatrix(RowIter)(ColIter).Text)
next
next
- Marcus
That's true, however some columns are for display only, and cannot be exported (breaks the specification).
So I need to somehow specify each column I shall export.
(Preferably without relying on the column number.)
Cheers,
Vegard
Maybe other ways are easier, for example to use specialized objects for exports, reporting and printing on a hidden sheet (almost ever I use this approach) or to export objects completely and remove unnecessray content within excel, maybe in a master-file which included such a routine in an OnClose trigger.
- Marcus