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?
HI, How did you create your template?
Hi Lech - thanks for the reply. Here are my responses:
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.
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?
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.
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.
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
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.
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.
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.