Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'....
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
is the a way to pass that to TextBox?
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.
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
Thanks Marcus but how this by used in a way to give all objects in sheets?
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
;
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