Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

List objects in Sheets

Is there a way to have  a list of all objects(let say in Textbox) that are currently in the file?

Let say comma separated ' 'CH01','LB01'....

7 Replies
Anonymous
Not applicable

Try starting from here, it should help:

sub test
set sheet = ActiveDocument.Sheets("SH01")
For i = 0 to 50

set so = sheet.SheetObjects(i)
id = so.GetObjectId
msgbox(id)

next
end sub

robert_mika
Master III
Master III
Author

is the a way to pass that to TextBox?

Anonymous
Not applicable

What you can do in a macro is to assign result to a variable.  After this you can show it in a text box of course.

marcus_sommer

You could also read the meta-data from the qvw and from layout-export and use these as tables and fields:

/****************************************************************************************************************************************************************

Beginn DocumentsObjects - Auslesen Attribute SOE (Sheet, Objects & Expressions + Groups, Reports, Bookmarks, Variables) aus QVW's (Metadaten XML-Struktur)

*****************************************************************************************************************************************************************

Aufgabe:

- Lädt die ausgewählte Meta-Daten dieser Anwendung ein

Hintergrund:

- schneller und direkter Zugriff auf die Objektstruktur dieser Anwendung um die Sheet-Oberflächen besser entwickeln zu können, z.B. welches Objekt befindet sich

  auf welchem Sheet

- QlikView-Anwendungen sind eine Mischung zwischen propertieren Datenstrukturen und XML-Datenstruktur, so daß nicht alle Meta-Daten aus der Anwendung selbst

  ausgelesen werden können --> die hier fehlenden Meta-Daten können aber über den Export von Struktur-und Layout-Daten anschließend wieder eingelesen werden

- Export dieser Daten kann Zentral über ein separates Steuerungsfile und/oder direkt in dieser Anwendung per Makro ausgelöst werden (nicht alle Datenbereiche

  sind GUI erreichbar)

Datenquelle:

- diese Anwendung selbst --> Documentname()

- ....\DataStructures\AppLayout\AppLayout_' & DocumentName() & '.xml'

ToDo:

- Aufnahme weiterer Meta-Daten, z.B. Mem-Files

---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

let vTabName = 'DocumentObjects';

let vOwnPath = DocumentPath(); // Pfadvariable - eigener Pfad

let vLayoutPath = '$(pApp@01)' & 'DataStructures\AppLayout\AppLayout_' & DocumentName() & '.xml'; // Pfadvariable zum Layout-Source-File

let vLayoutStoredPath = '$(pApp@01)' & 'DataStructures\AppLayoutQVD\' & DocumentName(); // Pfadvariable zum Layout-Stored-File

if '$(vLoadDocumentObjects)' = 'current' then

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------

GROUPS auslesen - nicht direkt an SOE gejoint, da der Name zur Gruppe gejoint wird

---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    Groups:

    LOAD upper(filepath()) as FileName, 'Group' as SOE_Type, Name as %ObjectId, if(IsCyclic='true','Cyclic','Hierarchic') as Definition,

         filetime() as CreatedDate, %Key_GroupDescription_CB92F98E8506AF37    // Key for this table: DocumentSummary/GroupDescription

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/GroupDescription]);

        join

    LOAD Name as Label, %Key_GroupDescription_CB92F98E8506AF37    // Key to parent table: DocumentSummary/GroupDescription

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/GroupDescription/FieldDefs/FieldDefEx]);

    drop field %Key_GroupDescription_CB92F98E8506AF37;

    $(Include=$(pScript@04)Include_LoadLogging.txt);

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------

SOE's auslesen

---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetObjects:

    NoConcatenate Load * Resident Groups; // Groups

        OUTER JOIN

    LOAD upper(filepath()) as FileName, SheetId as %ObjectId, 'Sheet' as SOE_Type, Title as Label // Sheets

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Sheet]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, REPLACE(ObjectId,'Document\','') as %ObjectId, Type as SOE_Type, Caption as Label // Sheet Objects

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/SheetObject]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, %ObjectId, 'Expression' as SOE_Type, Definition, Label // Expressions

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Expression]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, %ObjectId, 'Dimension' as SOE_Type, PseudoDef as Definition, Title as Label // CalculatedDimension

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Dimension]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, %ObjectId, 'Variable' as SOE_Type, RawValue as Definition, Name as Label // VariableDescription

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/VariableDescription]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, Id as %ObjectId, 'Report' as SOE_Type, Name as Label // Report

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Report]) WHERE Id > '';

        OUTER JOIN

    LOAD upper(filepath()) as FileName, BookmarkId as %ObjectId, 'Bookmark Item' as SOE_Type, BookmarkField as Label // BookmarkItems

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/BookmarkItems]);

        OUTER JOIN

    LOAD upper(filepath()) as FileName, BookmarkId as %ObjectId, 'Bookmark' as SOE_Type, Name    as Label // Bookmark

    FROM $(vOwnPath) (XmlSimple, Table is [DocumentSummary/Bookmark]);

    store SheetObjects into $(vLayoutStoredPath)_SheetObjects.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

    drop table Groups;

/*---------------------------------------------------------------------------------------------------------------------------------------------------------------

Zuordnung von Objekten zum Sheet generieren --> aus dem exportierten App_Layout

---------------------------------------------------------------------------------------------------------------------------------------------------------------*/

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    FrameParentDef:

    LOAD replace(ObjectId, 'Document\','') as %ObjectId, %Key_SheetProperties_CD0C067A8D4AC977

    FROM $(vLayoutPath) (XmlSimple, Table is [DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties/ChildObjects/FrameParentDef]);

    store FrameParentDef into $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

   

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetProperties:

    LOAD Name as SheetName, replace(SheetId, 'Document\','') as SheetId, //'Sheet' as SOE_Type,

        %Key_SheetProperties_CD0C067A8D4AC977    // Key for this table: DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties

    FROM $(vLayoutPath) (XmlSimple, Table is [DocLayout/AllProperties/LayoutProperties/LayoutProperties/SheetProperties]);

    store SheetProperties into $(vLayoutStoredPath)_SheetProperties.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

   

elseif '$(vLoadDocumentObjects)' = 'stored' then

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetObjects:

    Load * From $(vLayoutStoredPath)_SheetObjects.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    FrameParentDef:

    Load * From $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetProperties:

    Load * From $(vLayoutStoredPath)_SheetProperties.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

   

end if

/****************************************************************************************************************************************************************

Ende DocumentObjects

****************************************************************************************************************************************************************/

let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetObjects:

    Load * From $(vLayoutStoredPath)_SheetObjects.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    FrameParentDef:

    Load * From $(vLayoutStoredPath)_FrameParentDef.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

    let vLoadStart = num(now() - today(), '#.########', '.', ','); // Beginn des Load-Vorganges - gehört zur Routine des Load-Logging

    SheetProperties:

    Load * From $(vLayoutStoredPath)_SheetProperties.qvd (qvd);

    $(Include=$(pScript@04)Include_LoadLogging.txt);

   

end if

/****************************************************************************************************************************************************************

Ende DocumentObjects

****************************************************************************************************************************************************************/

- Marcus

robert_mika
Master III
Master III
Author

Thanks Marcus but how this by used in a way to give all objects in sheets?

Anonymous
Not applicable

Hi Robert

in the same directory where you application is create a folder and name it like so "AppName-prj"

save open and save your App

if you open the prj folder u will see a number of xml files created

place script somewhere on you App in the load script or create  a separate App for testing purposes and place the following code in the load script

make use of fields generated

if u have any queiries let me know

Set vFolder = 'Path to your prj folder\test-prj';

for each File in filelist(vFolder& '\*CH0*.xml') //file names of all objects you want to bring in

EXPRESSIONDATA:

LOAD

RowNo() as Position, filename() as fileName,

[GraphLayout/Frame/Name/v] as ChartName,

[_ExpressionVisuals/ExpressionVisualDef/Label/v] as ExpressionName,

  

  [_ExpressionDatas/ExpressionData/Definition/v]as ExpressionCalc,

   

   

[_ExpressionVisuals/ExpressionVisualDef/Label/v]

&': ' &[_ExpressionDatas/ExpressionData/Definition/v]

as

Expression

,

left(filename(),4) as ObjectID

FROM $(vFolder)\*CH0*.xml (XmlSimple, Table is [GraphProperties]);

// End of [CH01.xml] LOAD statements

CHARTDATA:

LOAD

[ChartProperties/Title/Title/v]as ChartTitle,

[TableProperties/ExpansionInfo/ExpansionData/Pos/DimName] as DimensionName,

   

[Dimensions/ChartDimensionDataDef/PseudoDef/Name] as DimensionCalc

   

,   

left(filename(),4) as ObjectID

FROM $(vFolder)\*CH0*.xml (XmlSimple, Table is [GraphProperties]);

// End of [CH01.xml] LOAD statements

exit Script;

left

join

(CHARTDATA) load

ObjectID

,replace

(ObjectID

, 'CH','') as

CHTID

, concat

(distinct

DimensionCalc

) as

DimensionsConcat

REsident

CHARTDATA group

by

ObjectID

;

left

join

(EXPRESSIONDATA) load

ObjectID

,

concat

(distinct

Expression

,'

' ) as

ExpressionsConcat

REsident

EXPRESSIONDATA group

by

ObjectID

;

marcus_sommer

My approach includes more than the object-id's and therefore the script is a bit longer and more complex. But you could have deeper insights in your apps. Results from my script are:

- Marcus