Macro call to oCol.Name fails for expressions

[vegard_bakke]

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

[petter]

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 .....

[vegard_bakke]

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

[marcus_sommer]

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

[vegard_bakke]

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

[marcus_sommer]

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