topic Re: setting column widths using a macro in QlikView

setting column widths using a macro

Tue, 04 Jun 2013 21:50:03 GMT

I have a chart, which is really a table of data with a bunch of columns. I'd like to set the column widths of a bunch of columns to the same width. I looked through these discussions and it seemed that a macro was the way to go. I tried copying some examples, adapting them (so I thought) to my situation. One used:

set ch = ActiveDocument.GetSheetObject("CH01")

ch.SetPixWidth 0, 50

When I clicked TEST, it didn't like the SetPixWidth.

I tried another that had:

set obj = ActiveDocument.GetSheetObject("CH01")

set objProp = obj.GetProperties

set objDim = objProp.Dimensions

objDim(i).ColWidth = 0

The above shows the lines that I used and changed for my sheet object number. It didn't like the last statement.

I know I have no idea what the object model is for QlikView and I'm really just guessing. I don't know what the members of a sheet object are. Can anyone either help me with the specific problem of setting a bunch of columns to a specific width, or direct me to some documentation on the object model that might help?

Mitch

Re: setting column widths using a macro

marcus_sommer — Wed, 05 Jun 2013 11:14:09 GMT

Hi Mitch,

it is a extended version of your trying with a loop through variables to control the width from specific columns. I think it will be helpful.

sub PrepareColumn

dim doc, obj, varColumnList, varColumn, varColumnVisible, varColumnWidth, i, ii

set doc = ActiveDocument

set obj = doc.GetSheetObject("CH10012")

varColumnVisible = doc.Variables("vHelpColumnVisible").GetContent.String

if varColumnVisible = "on" then varColumnWidth = 40 else: varColumnWidth = 0

varColumnList = doc.Variables("vHelpColumnList").GetContent.String

varColumn = split(varColumnList, ";")

for i = 0 to ubound(varColumn)

if instr(varColumn(i), "-") = 0 then

obj.SetPixWidth varColumn(i), varColumnWidth

else

for ii = cint(left(varColumn(i), instr(varColumn(i), "-") - 1)) to cint(mid(varColumn(i), instr(varColumn(i), "-") + 1))

obj.SetPixWidth ii, varColumnWidth

end if

end sub

- Marcus

Re: setting column widths using a macro

Fri, 07 Jun 2013 18:33:45 GMT

Hi Marcus,

Thanks for the answer. The problem that I'm having, however, is that I can't get the obj.SetPixWidth past the "Test" button in the macro window. I've got the following:

public sub MyHide
call HideColumn("CH26",5)
end sub

private sub HideColumn(ch,n)
set ch = ActiveDocument.GetSheetObject(ch)
ch.SetPixWidth (n), 50
end sub

I get "Object doesn't support this property or method: 'ch.SetPixWidth'"

I am using a stand-alone version so I don't have a server. I don't know if that has anything to do with it or what?

Any suggestions?

Mitch

Re: setting column widths using a macro

marcus_sommer — Sat, 08 Jun 2013 08:59:09 GMT

Hi Mitch,

I think it is the brackets around parameter "n". I don't sure whether the reuse from "ch" in this case proper worked, try this:

private sub HideColumn(ch,n)

dim obj

set obj = ActiveDocument.GetSheetObject(ch)

obj.SetPixWidth (n), 50

end sub

- Marcus

Re: setting column widths using a macro

phcaptjim — Fri, 14 Jun 2013 15:22:51 GMT

Marcus I have tried your logic on a PIVOT table and it works great. However, when I try and apply that same logic to a straight table it returns the error mentioned above. Is there a way for this to work with straight tables? Perhaps that is the issue I am having......

Thanks

Re: setting column widths using a macro

marcus_sommer — Fri, 14 Jun 2013 15:40:21 GMT

Take for a straight table SetPixWidths instead SetPixWidth - see also the examples in APIguide.qvw.

- Marcus

Re: setting column widths using a macro

phcaptjim — Fri, 14 Jun 2013 15:51:27 GMT

Marcus, thanks! Works perfectly!