How to change the pivot table in Nprinting Excel by Using Macros or any other methods

[SNR1]

HI All,

How to change the pivot table in Nprinting Excel by Using Macros or any other methods

in qlikview nprinting

I want to change my pivot table in Nprinting excel report without cloning/changing the pivot table into straight table in qlikview.

how to run macro automatically before opening the excel report in Qlik-Nprintnig.

Thanks In ADvance

Nikhil S

[Lech_Miszkiewicz]

Provide information regarding version of your environment:

read this:

https://help.qlik.com/en-US/nprinting/April2018/Content/Introduction/Introduction.htm

there is a table which tells you what is / is not supported in particular versions. So as you can see your environment might not support macros at all if it is based on NPrinting 17 and higher

cheers

Lech

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[SNR1]

Hi Lech , I,m Using Nprinting 16 only. Thanks in Advance

[Ruggero_Piccoli]

Hi,

Qlik NPrinting 16.* supports Microsoft Office report templates with macros. Qlik NPrinting will copy the VBA code from the template to the generated report but, for security reasons, never executes it.

The user who opens the report must run the macro by himself or you can associate the macro with the document opening event.

Another consequence is that if you export an Office report in PDF format the macro is not executed and the generated report has not the macro's effects.

Note that Qlik NPrinting 17+ doesn't supports Office macros at the moment (April 2018) so creating templates with macros could be a limitation in case you want to update your installation to 17+.

Best Regards,

Ruggero

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Lech_Miszkiewicz]

Hi Ruggero,

just out of curiosity - have you ever tried to implement macro which gets executed prior to document save?

I think I have done it few times (with success also in NPrinitng 17 although I know it is not supported). I have tested a solution where excel pivot table would get updated prior to be produced as pdf report....

just like in this topic:

Re: Nprinting Pivot table not shown in pdf report

and it worked as the macro was executed at the stage where temporary report files were created. I have not tested this for a long time so I am unsure if it would still work.

regards

Lech

[Ruggero_Piccoli]

Hi Lech,

In Qlik NPrinting 17+ the Office file formats that support macros are not available as output format when you create a task (maybe they, or some of them, will be available in a near future...maybe). You cannot store VBA code into normal Office file. For example, if you add a macro into an Excel file and try to save it as .xlsx you'll get an error.

Second reason why macros cannot work is that the VBA code is not copied from the template to the generated reports by Qlik NPrinting.

There are not workaround at the moment, we need to wait the release of the official support for Office macros.

Ciao

Ruggero

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT https://community.qlik.com/docs/DOC-14806. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Lech_Miszkiewicz]

Hi Ruggero. I totaly agree with you. 

In my example the task output was pdf. I also did a trick by navigating to template in windows explorer and changing template file using normal ms excel to format with macro. I am not even sure if it was just xls (like office 2007 with macro format). I did not edit template in nprinting designer after this and it seemed to execute macro. 

I am not going to argue about this, but in my opinion it worked as macro in the excel supposed to refresh pivot table which later was sent as pdf. And it worked back then. I did not try to do this recently. 

This was quite time ago and i am not supporting this “hacky” method. I just mentioned it more as a interesting hint or idea which could be used once macros will ne officialy supported

Ciao!

[Ruggero_Piccoli]

Thanks 🙂

I hope we will have macro official support soon so we will not need to find hack any more.

Ciao