7 Replies Latest reply: Apr 29, 2015 1:18 AM by Marcus Sommer RSS

    List objects in Sheets

    Robert Mika

      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'....

        • Re: List objects in Sheets
          Michael Solomovich

          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

            • Re: List objects in Sheets
              Robert Mika

              is the a way to pass that to TextBox?

                • Re: List objects in Sheets
                  Michael Solomovich

                  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.

                  • Re: List objects in Sheets
                    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

                      • Re: List objects in Sheets
                        Robert Mika

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

                          • Re: List objects in Sheets
                            Farai HURUBA

                            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

                            ;

                            • Re: List objects in Sheets
                              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