topic Re: Export to Excel Macro in QlikView

Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 14:57:27 GMT

Hi Qlikers

I am in a need for a macro which would be linked to a button action to export a particular table box object into a Excel file. I have seen many Macros on the community, but I am finding it hard to configure them for my need. I was wondering if somebody can provide a working sample of a macro which does the above mentioned thing.

Thanks in advance for your support.

Best,

Sunny

Re: Export to Excel Macro

alexandros17 — Tue, 09 Jun 2015 15:03:14 GMT

This is a very simple export:

SUB EXPORT
set sObject1 = ActiveDocument.GetSheetObject("CH_EXPORT")
sObject1.Export "E:\MyDoc.csv", ", "
end sub

CH_EXPORT is the name of the object containing the data (Your table)

Re: Export to Excel Macro

rubenmarin — Tue, 09 Jun 2015 15:12:49 GMT

Hi Sunny, Alessandro answer is correct (as usual ). If you want more documentation there is an app with useful help about automation: QlikView Core COM API Guide - Version 11

Re: Export to Excel Macro

petter — Tue, 09 Jun 2015 15:20:27 GMT

There is actually (at least) three more API-functions to do export:

ExportBIFF

ExportEx

ServerSideExportEx

Which gives you a selection of more paramters and file-formats....

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 15:47:16 GMT

Thanks for a very quick response Alessandro Saccone. What if I want to open the data in Excel instead of saving it? This way I can let the user save it wherever they like it to be saved.

My another question is, will this button (with macro action) will work on the portal version or will this only work on the desktop version of the application?

Best,

Sunny

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 15:48:27 GMT

I have looked at that qvw document, but somehow the only macro related to Export Excel I found seemed very complicated and it did not work for me. But thanks for sharing that information Ruben Marin‌

Best,

Sunny

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 15:49:56 GMT

Thanks for your response Petter Skjolden‌, Is there any place I can see examples of them being implemented? I need the macro to open the excel file instead of saving it in pre-defined location. Is that possible? and will the macro work on the portal as well?

Best,

Sunny

Re: Export to Excel Macro

petter — Tue, 09 Jun 2015 15:59:55 GMT

If you use the ServerSideExportEx it will be stored on the server as the API-function name implies. Then the location you store it to has to be a location that can be reached with a URL. That should be easy to configure or setup.

I am not sure if it is possible in the very same Macro to launch the URL though.

Re: Export to Excel Macro

buzzy996 — Tue, 09 Jun 2015 16:26:00 GMT

see attached in sheet1 export button..u have to modify a bit accordingly ur needs..but it won't work on access point,hopes helpful.

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 16:28:44 GMT

No the image Shiva, need to export out the data from a table box. Can the macro be edited to do that?

Re: Export to Excel Macro

buzzy996 — Tue, 09 Jun 2015 16:38:16 GMT

hope so,let me try.

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 16:39:23 GMT

Thanks Shiva

Re: Export to Excel Macro

Colin-Albert — Tue, 09 Jun 2015 16:43:43 GMT

I would take care in exporting from a table box, as unless you include a unique key against each row of data, a table box will only export the unique data values. So if your data has any duplicates, the table box will contain a single row for the duplicates not all rows.

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 16:49:02 GMT

Thanks for your response Colin Albert‌.

The users have been exporting the data from table box, the problem is that it has become too large for a desktop computer to handle is that, I thought that if they want to export it out, a macro might prove useful. But the macro that Alexandros17 provided, somewhat does the job, but still takes a lot of time to do.

I am thinking of taking an alternate route where I only display the table box after a certain number of selections has been made.

Let me know if you or anyone else have other ideas.

Best,

Sunny

Re: Export to Excel Macro

sunny_talwar — Tue, 09 Jun 2015 17:35:11 GMT

Thanks guys for your input. I ended up using a calculation condition where I restricted the number of line items by 1,048,576 (which is the number of rows of data allowed to exported into a Excel). It seems to work for me and I think users will understand why I was required to do what I did.

Best,

Sunny

Re: Export to Excel Macro

Colin-Albert — Wed, 10 Jun 2015 08:35:24 GMT

Hi sunindia.

You can configure the error messages on your chart to be more helpful to the users. When I add a row limit, I change the error message for "Calculation Condition Unfulfilled" to something like

='Please filter the data to select fewer than ' & $(vRowLimit) & ' rows to view this chart

You currently have ' & num(count(data_field_name), '#,##0') & ' rows selected'

The only drawback this can have is that the message font size cannot differ from the chart font size, so sometimes it is better to use a text box, with a conditional show, that overlays the chart to display this message, in addition to the calculation condition on the original chart.

Re: Export to Excel Macro

rubenmarin — Wed, 10 Jun 2015 13:40:40 GMT

Hi Sunny, the example of exportbiff for a tablebox is very simple, I would say it's too much easy.

I read you touched the row-limit in Excel, maybe using csv you can avoid the limit.

For more customizations while exporting the excel I usually create a macro in Excel with the actions I want to do in my QV-Macro, then I use the code created in Excel as a base code to develop the macro.

Re: Export to Excel Macro

sunny_talwar — Wed, 10 Jun 2015 14:00:38 GMT

Hey RubenMarin‌ thanks for your input.

Problem is I have very limited expertise with Excel Macros or QV Macros (I wish I paid more attention to them while in school), so it would be hard for me to implement.

Second issue with the Macro is that it won't allow the user to export from the portal, so I guess there is no point creating this Macro as half the users are going to view this application from the portal (as they only have viewer license)

Best,

Sunny

Re: Export to Excel Macro

Colin-Albert — Wed, 10 Jun 2015 14:06:00 GMT

This post has a good overview of the issues of macros with the Access Point.

Macros are Bad