topic Re: Help with a pretty big macro in QlikView

Help with a pretty big macro

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone!
I've made a macro to export to Excel some pivot tables from QlikView. It works well, but due to the heavy formatting required by the client, it takes quite a while to execute. How can I open a template Excel file that already has all the formatting required by the client, paste my tables in the template and then save the Excel file as a separate file, in VBScript? At your request, I can provide some excel templates.

Re: Help with a pretty big macro

big_dreams — Thu, 08 Feb 2018 07:27:15 GMT

Why don't you try with NPrinting??

Regards,

Re: Help with a pretty big macro

Anonymous — Thu, 08 Feb 2018 07:48:34 GMT

The client doesn't want to spend any more money than he already had, so until the PM convinces him otherwise, I have to do the best I can with what I have right now

Re: Help with a pretty big macro

tamilarasu — Thu, 08 Feb 2018 08:02:45 GMT

Mihai Iov wrote:

At your request, I can provide some excel templates.

Please post some sample test file and expected output.

Re: Help with a pretty big macro

Anonymous — Thu, 08 Feb 2018 08:26:14 GMT

Here are the templates and the expected outputs. The pivot tables' IDs are stored in an array and pasted in the macro. All I want to know is how to open the templates in VBScript, populate them with the pivot tables from QlikView and then saving them as separated files

Re: Help with a pretty big macro

tamilarasu — Thu, 08 Feb 2018 09:29:32 GMT

Please attach your qvw file as well. Also, I would like to see your existing macro. If possible, I will try to reduce the time by modifying your macro code or else will try to paste the data in the above posted templates.

Re: Help with a pretty big macro

Anonymous — Thu, 08 Feb 2018 09:37:49 GMT

Here's the qvw. The macros are available in the Edit Module
Regarding pasting the data into the templates, is there any way of doing it by keeping the formatting of the cells(the inner color, lines, etc.)?

Re: Help with a pretty big macro

tamilarasu — Fri, 09 Feb 2018 08:02:07 GMT

Mihai - I will check and get back to you today for sure. Hope this is fine with you.

Re: Help with a pretty big macro

Anonymous — Fri, 09 Feb 2018 08:21:30 GMT

It's ok. My main issue right now is trying to figure out a way to paste the tables from QlikView into the Excel templates without ruining the formatting of the excel templates.

Re: Help with a pretty big macro

jonathandienst — Fri, 09 Feb 2018 08:22:27 GMT

You may get more detailed help from an Excel forum - everything derived from CreateObject("Excel.Application") is Excel. The forum might help you find the correct API methods, but you may have to translate the VBA code to VB script.

When you post there, you will need to extract the module code and post that.

Re: Help with a pretty big macro

tamilarasu — Fri, 09 Feb 2018 08:34:09 GMT

Even if we paste the data into the excel, we have to do some formatting i.e cell merge, rows are not fixed so we should make sure that the should not overwritten by data etc. I'll try my level best and get back to you.