Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

limingthefirst
New Contributor III

Export sheetID and ObjectID to excel file

Hi Everyone,

I have a request is that export all sheetIDs and ObjectIDs to an excel file using macro, but I am new in VBS, could anybody give some sugesstions?

20180411203957.png

Tags (1)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Export sheetID and ObjectID to excel file

Here is the script to export a list of sheets and objects to a csv file for the current document.

LET vDocumentPath = DocumentPath();

SheetObject:

LOAD SheetId,

    Title as SheetTitle,

%Key_Sheet_A4D568A6CD8BD40A

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

Left Join (SheetObject)

LOAD ObjectId%Table as ObjectId,

    %Key_Sheet_A4D568A6CD8BD40A

FROM [$(vDocumentPath)] (XmlSimple, Table is [DocumentSummary/Sheet/ChildObjects/ObjectId]);

DROP Field %Key_Sheet_A4D568A6CD8BD40A;

Left Join (SheetObject)

LOAD ObjectId,

    Caption as ObjectCaption,

    Type as ObjectType

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

STORE SheetObject INTO SheetObject.csv (txt);

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

8 Replies
berndjaegle
Contributor

Re: Export sheetID and ObjectID to excel file

Hi Li Ming,

I would try to extracte it with the Governance Dashboard App.

There is menue where you can find objects.

You could export it manually to Excel or try to store it as qvd in the script.

2018-04-11 14_44_24-Governance Dashboard 2.0.qvw.jpg

Best regards,

Bernd

sudeepkm
Valued Contributor III

Re: Export sheetID and ObjectID to excel file

you may use Document Analyzer created by rwunderlich

Document Analyzer V3.0 Update Available

It has a tab that shows Object details. The Doc analyzer file size is also small (downloadable).

vvira1316
Valued Contributor II

Re: Export sheetID and ObjectID to excel file

You may want to understand Document Analyzer from rwunderlich

In this when an application is analyzed it captures various information that includes sheet ids and object ids.

I hope this helps.

Highlighted
limingthefirst
New Contributor III

Re: Export sheetID and ObjectID to excel file

Thank all your sugesstions~


I can not open the download link in qlik for Governance Dashboard App...

Then I try to use Document Analyzer, and this is not what I wanted.


Just use macro to export sheetID and ObjectID when the qvw had been reloaded by QMC~


20180411212715.png


Partner
Partner

Re: Export sheetID and ObjectID to excel file

Hi

Not sure about exporting to excel, but you can export to txt file

Go to Settings--->Expression Overview

Select Chart Expression

Click on Columns and select the below


Columns.png

and then Select Export

Export in a txt file

MVP & Luminary
MVP & Luminary

Re: Export sheetID and ObjectID to excel file

What are you going to do with the information?

Easier than a macro -- which is can be difficult to run reliably on the server -- is to load the SheetIds and ObjectIds using QlikView script and STORE in a csv file.

-Rob

MVP & Luminary
MVP & Luminary

Re: Export sheetID and ObjectID to excel file

Here is the script to export a list of sheets and objects to a csv file for the current document.

LET vDocumentPath = DocumentPath();

SheetObject:

LOAD SheetId,

    Title as SheetTitle,

%Key_Sheet_A4D568A6CD8BD40A

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

Left Join (SheetObject)

LOAD ObjectId%Table as ObjectId,

    %Key_Sheet_A4D568A6CD8BD40A

FROM [$(vDocumentPath)] (XmlSimple, Table is [DocumentSummary/Sheet/ChildObjects/ObjectId]);

DROP Field %Key_Sheet_A4D568A6CD8BD40A;

Left Join (SheetObject)

LOAD ObjectId,

    Caption as ObjectCaption,

    Type as ObjectType

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

STORE SheetObject INTO SheetObject.csv (txt);

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

limingthefirst
New Contributor III

Re: Export sheetID and ObjectID to excel file

Hi Rob,

Thanks your suggestion, it worked~