Convert TableBox to StraightTable with Macro Solution

hugmarcel — Sat, 16 Nov 2024 18:41:35 GMT

'Copy theTableBox as new straight table, use theExcludeField of theTableBox as dimension or expression, if not ""
'Call CopyTableBox2NewStraightTable("TB01", true, false, "<expressionfield>")
'Call CopyTableBox2NewStraightTable("TB02", false, true, "<dimensionfield>")
sub CopyTableBox2NewStraightTable(theTableBox, asDimensions, asExpressions, theExcludeField)
'Create new chart:
set chart = ActiveDocument.Activesheet.CreateStraightTable

'Read theTableBox items, add to new chart
set tb = ActiveDocument.GetSheetObject(theTableBox)
set tbp = tb.GetProperties
set cols = tbp.Layout.ColLayouts

for i = 0 to tb.GetColumnCount-1
    set fld = tb.GetField(i)
vColumnField = fld.Name
    vColumnLabel = fld.Name
    If (Len(cols.Item(i).Label.v) > 0) Then

      vColumnLabel = cols.Item(i).Label.v
    End If

'Set items either as dimensions or as expressions
If (asDimensions) Then
   'Add dimension
   If (vColumnField <> theExcludeField) Then
     chart.AddDimension vColumnField

     'Add label to dimension if <> dimension
     If (vColumnLabel <> vColumnField) Then
       set cp = chart.GetProperties
          set dims = cp.Dimensions
          dims(i).Title.v = vColumnLabel
          chart.SetProperties cp
       set cp = Nothing
          set dims = Nothing
        End If
      Else
     'Add dummy expression, finally
     chart.AddExpression "=Only([" & vColumnField & "])"

     'Set expression name and total mode
     set cp = chart.GetProperties
     j = cp.Expressions.Count - 1
     cp.Expressions.Item(j).Item(0).Data.ExpressionVisual.Label.v = vColumnLabel
     cp.Expressions.Item(j).Item(0).Data.ExpressionData.UsePartialSum = false
        chart.SetProperties cp
      End If
ElseIf (asExpressions) Then
   'Set dimension to chart
      If (vColumnField = theExcludeField) Then
        chart.AddDimension theExcludeField
     set cp = chart.GetProperties
        set dims = cp.Dimensions
        dims(0).Title.v = vColumnLabel
        chart.SetProperties cp
     set cp = Nothing
        set dims = Nothing
   Else
     'Set new expression
     chart.AddExpression "=Only([" & vColumnField & "])"

     'Set expression name and total mode
     set cp = chart.GetProperties
     j = cp.Expressions.Count - 1
     cp.Expressions.Item(j).Item(0).Data.ExpressionVisual.Label.v = vColumnLabel
     cp.Expressions.Item(j).Item(0).Data.ExpressionData.UsePartialSum = false
        chart.SetProperties cp
      End If
End If
Next

'Apply table box format to chart format
'Do it manually once new straigt table exists.

'Finish
set fld = Nothing
set cols = Nothing
set tbp = Nothing
set tb   = Nothing

set chart = Nothing
end sub

Re: Convert TableBox to StraightTable with Macro Solution

hugmarcel — Thu, 07 May 2020 22:10:49 GMT

I have copied some more items from the table box, such as window title, show condition, etc. However, I do not manage to copy the exact same Position (top, left, width, height) of the table box to the new straigt table. Maybe somebody knows how to do so:

'Copy tablebox title
set cp = chart.GetProperties
cp.GraphLayout.WindowTitle.v = tbp.Layout.Frame.Name.v

'Copy the calculation condition
cp.EvaluationCondition.v = tbp.EvaluationCondition.v

'Copy the show condition
cp.GraphLayout.Frame.Show.Always = false
cp.GraphLayout.Frame.Show.Expression.v = tbp.Layout.Frame.Show.Expression.v

'Copy the calculation errors messages
for i = 0 to cp.GraphLayout.ErrorMessageOverrides.Count-1
    cp.GraphLayout.ErrorMessageOverrides.Item(i).v = tbp.ErrorMessageOverrides(i).v
next

'Copy position: does not work, new chart does not take exact position of tablebox
'mh dim dummy(0) 'all parts
'mh set fr = chart.GetFrameDef
'mh set pos = fr.Rect
'mh pos.Top    = tbp.Layout.Frame.Rect.Top
'mh pos.Left   = tbp.Layout.Frame.Rect.Left
'mh pos.Width = tbp.Layout.Frame.Rect.Width
'mh pos.Height = tbp.Layout.Frame.Rect.Height
'mh set pos = fr.MinimizedRect
'mh pos.Top    = tbp.Layout.Frame.Rect.Top
'mh pos.Left   = tbp.Layout.Frame.Rect.Left
'mh pos.Width = tbp.Layout.Frame.Rect.Width
'mh pos.Height = tbp.Layout.Frame.Rect.Height
'mh chart.SetFrame fr, true, dummy

'Copy position: does not work, new chart does not take exact position of tablebox
pos = chart.GetRect
pos.Top    = tb.GetRect.Top
pos.Left   = tb.GetRect.Left
pos.Width = tb.GetRect.Width
pos.Height = tb.GetRect.Height
chart.SetRect pos

'Apply table box format to chart format
'Do it manually once new straigt table exists.
chart.SetProperties cp

topic Re: Convert TableBox to StraightTable with Macro Solution in QlikView

Convert TableBox to StraightTable with Macro Solution

Re: Convert TableBox to StraightTable with Macro Solution