Macro to get and set properties of a pivottable

[#1]

How can i get the value of a proppertie in a macro.

I made te folowing Macro:

sub FullyExpendTable
ActiveDocument.ActiveSheet.ActivateAll
objs=ActiveDocument.ActiveSheet.GetActiveSheetObjects
for i=lbound(objs) to ubound(objs)
set Prop = objs(i).GetProperties
If objs(i).GetObjectType = 10 Then 'pivot tables
if Prop.TableProperties.GetProperties.PivotAlwaysFullyExpanded=false
then
Prop.TableProperties.PivotAlwaysFullyExpanded=true
objs(i).SetProperties Prop
else
Prop.TableProperties.PivotAlwaysFullyExpanded=false
objs(i).SetProperties Prop
End if
end if
next
End Sub

And now i wanna know what the value is i want to set.

I thought that i could use the folowing if, but he is not working.

     if Prop.TableProperties.GetProperties.PivotAlwaysFullyExpanded=false

Who can help me with this Macro issue?

Greetz,

Dirk

[kaushiknsolanki]

Hi,

   Remove the GetProperties. Use it like this

   if Prop.TableProperties.PivotAlwaysFullyExpanded=false

Regards,

Kaushik Solanki

[#3]

Thank you,

The sollution is the same as i find out myself 5 minutes ago, but what if the language of the computer is different?

Greetz,

dirk

[Miguel_Angel_Baeyens]

Hello Dirk,

Do you mean the locale configuration (english, spanish) or you mean the VBscrip language?. The first case doesn't matter but the second does, check the Release Notes document of the version you are using to check for obsolete API calls, and use the APIguide.qvw file installed along with your documentation to see the current ones.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

I mean the language of your windows system.

Normaly the users in or company use dutch Windows versions.

But by exeption there are users who use English Windows versions.

A boolean in English is true or false

A boolean in Dutch is waar of onwaar

So my if would not work when i set hard "Waar" and the user use a Windows version in English.

Greetz,

Dirk

[Miguel_Angel_Baeyens]

Hello Dirk,

True or False (without quotes) in the QlikView API are not literals, but keywords or functions that result on a numeric value. So they will have to work with True or False regardless your operating system regional settings.  It's working with true and false in a macro using the Spanish Version of .NET and OS.

So the following macro code will show a popup with "True" (in your language) but it will work without changing it

Sub I
     If -1 = true Then
          MsgBox(true)
     End If
End Sub

Anyway, I'm sure there's a way to change it modifying your OS properties in some way (those related to .NET or VB) that I'm not aware of.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7 · Accepted solution]

That's correct,

My hardcoded true or false was wrong.

Thanks for the help

The total solution is:

sub FullyExpendTable
ActiveDocument.ActiveSheet.ActivateAll
objs=ActiveDocument.ActiveSheet.GetActiveSheetObjects
for i=lbound(objs) to ubound(objs)
set Prop = objs(i).GetProperties

If objs(i).GetObjectType = 10 Then 'pivot tables
if Prop.TableProperties.PivotAlwaysFullyExpanded =true then
Prop.TableProperties.PivotAlwaysFullyExpanded=false
objs(i).SetProperties Prop
else
Prop.TableProperties.PivotAlwaysFullyExpanded=true
objs(i).SetProperties Prop
End if
End if
next
End Sub