topic macro to list all charts on sheet in QlikView

macro to list all charts on sheet

Wed, 24 Feb 2010 16:30:59 GMT

hi all,

I'm trying to dynamically build buttons to access charts on another sheet. I am having trouble writing a macro to get a list of all of the charts on a particular sheet. I can get all of the objects, but how would I get a list of the charts and be able to access properties such as chart name, id and conditional expression for the visible property.

thanks so much,
Shanikia

macro to list all charts on sheet

Wed, 24 Feb 2010 16:43:19 GMT

You can get the info you want by utilizing GetSheetObjects and GetObjectType (see the API Guide for more detail).

Here is an example that will display each chart Id in a message box:

Sub GetCharts
 Objects = ActiveDocument.ActiveSheet.GetSheetObjects
 For i = lBound(Objects) To uBound(Objects)
 If obj.GetObjectType = 11 'Charts Then
 msgbox("Chart: " & obj.GetObjectId)
 End If
 next
End Sub

Once you have the object Id of the chart, you will use GetProperties. Then you can manipulate the properties and use SetProperties.

macro to list all charts on sheet

Wed, 24 Feb 2010 17:33:10 GMT

Thanks NMiller...

I thought I had the same syntax, but yours works and mine didn't so...just had to add an assignment:

For i = lBound(Objects) To uBound(Objects)

set obj = Objects(i)

obj.GetObjectType >= 10 Then ' All Chart types("Chart: " & obj.GetCaption.Name.v & " ID: "&obj.GetObjectId)

msgbox

End

macro to list all charts on sheet

marcel_olmo — Mon, 17 Jan 2011 18:01:03 GMT

Hey NMiller, thanks for your contribution.

I'm just wondering what if I would like to get all the objects of the document. Not only for the activated sheet.

How can I do that?

Many thanks in advance!!!

macro to list all charts on sheet

Mon, 17 Jan 2011 22:29:32 GMT

In order to get all objects, I think you would need to loop through all sheets and for each sheet use something similar to the routine above. GetSheetObjects is a method of a sheet, so you can't use ActiveDocument.GetSheetObjects.

Here is an example:

Sub GetCharts
 For j = 0 to numberOfSheets //I'm not sure how to determine this
 Objects = ActiveDocument.GetSheet(j).GetSheetObjects
 For i = lBound(Objects) To uBound(Objects)
 If obj.GetObjectType = 11 'Charts Then
 msgbox("Chart: " & obj.GetObjectId)
 End If
 next
 Next
End Sub

Here, you would loop through all sheets. I didn't find a property which defined how many sheets there were in the document, but there is probably a way to figure it out.

Re: macro to list all charts on sheet

krishnaroopa — Mon, 18 Jun 2012 09:06:08 GMT

set Objects = ActiveDocument.getSheet(0).GetSheetObjects

ActiveDocument.ActiveSheet.GetSheetObjects

Both is not working.. I am getting an error - "Object Required"

Please help

regards

KRS