Nprinting with external macro

[ronaldwang]

I have an Nprinting to export pivot table in to excel report. as i am not happy with the formatting so i can have some VBA written to change the format to what i want. will i be able to incorporate this macro into Nprint so that the output will be updated with the VBA to the format that i want? will be good if any example link can be shared

[Ruggero_Piccoli · Accepted solution]

Hi,

What version of Qlik NPrinting are you using?

As you can see in the first page of the help site https://help.qlik.com/en-US/nprinting/February2018/Content/Introduction/Introduction.htm Qlik NPrinting 16 supports Microsoft Office macros but Qli NPrinting 17 at the moment (February 2018) doesn't support Office macros.

So, if you are using the 17 you cannot add macros.

If you are using Qlik NPrinting 16 you can create an Office template that supports macros, for example an .xlsm file. Insert the VBA code in it and the code will be copied in all the output reports. The VBA code is never executed by Qlik NPrinting for security reasons. The recipient have to run the macro by himself or you can associate it with the opening event.

Best Regards,

Ruggero

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

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.

[ronaldwang]

Hi Ruggero,

I have created template that supports macros, but the Nprint seems to remove the macro during the process as the output will not have macro anymore, any idea? thanks

[Daniel_Jenkins]

Hi,

What version of NPrinting are you using? If 17.x or 18.x, as Ruggero mentioned above, macro enabled Office reports (XLSM, DOCM, PPTM) are not supported. If using 16.x, you need to set the output to the correct format. Example: Index on page loop excel

HTH - Daniel.

[ronaldwang]

thanks, I am using the 16. it must be the output format issue

[Ruggero_Piccoli]

Yes, in Qlik NPrinting 16 you must create a template in a format that supports macro (for example .xlsm) and select and output format that also supports macros (for example .xlsm).

If you select and output format that doesn't support macros the code will be removed.

Remember also that macros are never executed by Qlik NPrinting so:

- the user must manually run them

- as alternative you can trigger them on opening so it seems that they were executed

- if you select PDF as output format it will be generated without macros actions

[Ruggero_Piccoli]

Starting from September 2018 Qlik NPrinting can generate Excel, and only Excel, reports with VBA macros.

From the official Release Notes:

Excel reports generated in .xlsm format
Qlik NPrinting can generate Excel reports in .xlsm format. Visual basic macros can be created inside the Qlik NPrinting Designer and will be available in the final report. However, they are not executed.

[kswindlehurst]

I feel like Qlik is passing the buck on this topic.   Security-wise, if you are going to run a macro anywhere, running it as part of an NPrinting task within a far more controlled environment is immensely better than asking recipients to start trusting macro enabled email attachments.  That's a disaster waiting to happen.   And if corporate policies are done right, it'll be far easier to permit something to run on a particular server than on all the recipient PC's.   They'd be right to deny the latter.   The current NPrinting capability might be the only easy option, but it's not a very good one.   Now I get to talk my CFO out of using a macro to set his preferred expand\collapse settings for everyone.  And yeah, it's a daily report. 

[hoangvvo]

Personally agreed with qlik passing the buck.

Some limitation that can help alleviate having to create multiple tables for the purpose of ranking that is not necessary.

Example: I have an excel sheet (xlsx) that have multiple tabs, each that has a sort criteria will require a separate table in the qvf as the sort order is important, and the only way to do that is to create a new table with that sort criteria.

The data for all those exists and can be easily handle by excel using sort, ranks etc. and the data can be pull from a hidden table that is pull form the qvf. 

But to do that we need to make an xlsm add vba code. The user wants the file to be pdf automaticlaly with all sort , top 20 ranks etc.  and since nPrinting is not 'capable' of executing the macro that 'we' as developer created before outputing it as pdf, we as developer now have to create multiple tables in the qvf to meet the criteria required.

Also data coming from the qvf is static, that means rank created in the table in the qvf does not change if we change the date in our excel, the sumifs() of each cell checks the data in the data tab, but the rank does not update with the new 'criteria' as its static data.

nprinting would be extremely helpful and cut back development time if we can just execute macros during the pdf printing.