NP Excel Macro Missing - File Size Issue?

[tomdon14]

Hello -

I'm trying to execute a NP .xlsm report but after running in Preview mode the macro IS NOT included in the file. The file has ~350k rows and 34 columns.

If I reduce the row count to 300k rows the macro IS included.

If I reduce the column count to 20 the macro IS included.

Does NP have a limitation for running macros based on number of rows/columns or file size?

Thanks.

[Lech_Miszkiewicz]

HI, How did you create your template?

• Did you create first xlsm file with your macro using EXCEL and then used it as a template when you created your report?
  • see reference for steps - steps are in one of my answers in this topic: https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Extract-total-cells-from-all-Sheet-Level/m-...
• What is your macro supposed to do?
• When you click on PREVIEW i assume you click on: Preview as XLSM?

thanks

Lech

[tomdon14]

Hi Lech - thanks for the reply. Here are my responses:

• I created an empty .xlsm file, saved it, and then used it as a Custom Template when creating the NP report.
• For testing, I created a simple macro that displays a message box. The problem is that the macro/VBA code is not even present in the report if it is too large.
• Yes, I click on Preview-XLSM.

As mentioned, the report does work if not retrieving such a large number of records. I have not pin-pointed the exact size where it stops working, but a file size of ~32.2k KB works while a file size of ~50k KB does not display the VBA code.

Thanks for any insight you can offer.

[Lech_Miszkiewicz]

So far everything you have done is as it should be... At this stage I would only check if you are running supported configuration of NPrinting (check if Designer is running on the exact the same version as server) and check if your excel settings allow for macros. https://help.qlik.com/en-US/nprinting/April2020/Content/NPrinting/ExcelReports/Intro-Excel.htm

If you meet all requirements and it is still not working I suggest you open a support ticket with Qlik, or maybe @Ruggero_Piccoli can add to it? Anything I missed Ruggero?

Thanks

Lech

[Ruggero_Piccoli]

Hi,

I just did many tests on my installation and all the .xlsm files I generated via Qlik NPrinting Designer preview had the VBA macro code inside. So I'm not able to reproduce. 

It appears strange to me that the VBA code disappears based on the size of the produced report. There are not limitations on this. If you use a custom .xlsm template and run a preview in .xslm format you should ever get the VBA code copied in the generated reports. Please note that the VBA code is never executed during the report generation. 

If you continue to have the issue, please send a support ticket with everything needed to reproduce it. If you are using public data you could also post the data and the template here.

Best Regards,

Ruggero

[devan9876]

@Ruggero_Piccoli 

In regards to the macros not being executed during report generation, is that something that could be enabled in a future release or is it not possible?

I think it would be beneficial to be able to use macros to apply additional formatting prior to the file being saved. Either that or for Nprinting to allow us a way to dynamically color native PowerPoint/Excel charts/shapes.

[Ruggero_Piccoli]

Hi,

I think it will not be enabled for security reasons. The VBA code could be dangerous so Qlik NPrinting will never execute it. 

You could create a new feature request https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Best Regards,

Ruggero

[devan9876]

How would it be dangerous?

Wouldn't the file used as the template for Nprinting be asomething only those with the right permissions would be able to upload?

And even if it is potentially dangerous, why couldn't it be up to the end user to decide if they want to accept that risk rather than it being completely blocked?

I only have access to a community account and only partners/luminaries seem to be allowed to submit feature requests.

[Ruggero_Piccoli]

Hi,

I don't know if it is feasible to run the VBA code during report generation. I will add the feature request.

In any case, also Microsoft ask you if you want to run the VBA code or give you a security alert. You can configure the VBA code to be executed "On opening" event. So it will be executed the first time a recipients will open the .xlsm report. 

Best Regards,

Ruggero

[devan9876]

@Ruggero_Piccoli 

Thank you for submitting it for me.

I have looked into the "on open" event before but ideally I would like the macro to have run before the end user opens the file and also the on open event doesn't seem to be supported/ available in PowerPoint which is my preferred file type for certain reports.

Alternatively if macros can't be allowed to run during execution, would it be possible for NPrinting to allow us to specify background/text colors for native PowerPoint objects (shapes and charts)?

To be more specific..if I have a native PowerPoint textbox/shape being populated with text by nprinting, I would also like to set the background/text color of the textbox/shape.

Or if I am populating a native PowerPoint barchart with values from NPrinting I would also like to dynamically color the bars with Nprinting as well.