Qlik Community

QlikView Documents

Documents for QlikView related information.

Loop Charts and Field Values - Excel Export Macro

autopilot
Contributor III

Loop Charts and Field Values - Excel Export Macro

This is a modified expansion of my other utility found here: http://community.qlik.com/docs/DOC-4416

This vbscript macro that will export all (pivot and straight table) objects on a given sheet to an Excel workbook. Each worksheet will be named according to the chart id. It also contains the ability to control the use of an inner loop which when active will export each chart by each possible field value in the designated field. All of the export's contents will be contained within a single Excel workbook separated by worksheets using the naming convention "ChartId - FieldValue".

The purpose of this utility is to provide the ability to dynamically export chart objects while optionally looping through specific field values as well. The goal is to allow for easier (mass) extraction of information into Excel.

The variables of interest are as follows:

vfname - field to loop

vMacroChartId - long name chart id

vMacroChartId2 - short name chart id

vLoopFieldOption - 1 = loop field values; else escape inner loop

Attachments
Comments
Not applicable

First of all thanks for sharing your knowledge in the community. I've find really useful your code. But i would like you to answer a question: i've used your code (with a little change) to fill templates in an existing excel, i've used PasteSpecial in order to not change the format in the template, but in that way exports the numbers as text, and if i use only Paste the numbers are ok but the format in the template is not respected. I'm an absolutely newbie in VB, Do u know how todo it?

Thanks in advance.

0 Likes
autopilot
Contributor III

I'm glad that this was helpful for you, Pablo.

Let me make sure I've understood correctly. You want to be able to use this script and export to an excel template vs a new workbook, correct? And also, retain the number format as set within the qlikview object?

I've used this concept with powerpoint before where the export targeted a template so I'm sure that we can adjust this to do the same for excel to meet your purposes.

Can you provide a couple of details....what is the excel file's extension (i.e. .xlsx,.xltx,.xls,.xlt)?....what is the format you set in qlikview? in your template?.......do you mind providing your vbscript code?


Not applicable

Thanks for your interest!!! I've been able to solve my problem. It was just a formatting number problem. Just changing the format in the table (coma instead of dots, and dots instead of coma). Yor script it's been really useful. Thanks.

0 Likes
Not applicable

Hi,

Do you know how to export each qlikview sheet into different slides in powerpoint using macro?

0 Likes
autopilot
Contributor III

I published something previously that automates an extraction to powerpoint (link below).

http://community.qlik.com/docs/DOC-3774

The solution is based on extracting charts to ppt slides....but if you're idea would be to extract a screenshot image of the qv sheet, then this could be easily adapted to achieve that purpose.

Not applicable

Thanks. Will look on it.

0 Likes
Not applicable

Macro does  not work in webview mode

0 Likes
autopilot
Contributor III

Hi Gaurav,

This is correct. From qlikview's release notes: "Macros will not work in WebView."

0 Likes
Not applicable

hello everyone, can you plz tell me what is to provide the variable vLoopFieldOption, and vchartID2.

0 Likes
vikasmahajan
Esteemed Contributor

If this is not working in WEB VIEW  what is the use of this utility ?

Thanks

Vikas

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-09-17 04:42 PM
Updated by: