Qlik Community

Ask a Question

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
March 9, 10AM EST: Live Chat, Bring your NPrinting Questions! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomdon14
Contributor III
Contributor III

NP Excel Macro Missing - File Size Issue?

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.

Labels (2)
10 Replies
Lech_Miszkiewicz

HI, How did you create your template?

thanks

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
tomdon14
Contributor III
Contributor III

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

1.png

 

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Ruggero_Piccoli
Employee
Employee

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



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 with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
devan9876
Creator
Creator

@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
Employee
Employee

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



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 with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
devan9876
Creator
Creator

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
Employee
Employee

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



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 with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
devan9876
Creator
Creator

@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.