Export QlikView sheet to Excel workbook with formulas

[#1]

I am new to QlikView and I need your help.

I wouldn't ask, if I could've found a sort of solution to similar problem, but I found only questions and no answers.

I developed a what-if analysis (a recovery plan), which works as expected. My customers are happy, but they requested functionality to export entire QlikView sheet to Excel sheet with the formulas. It is a critical requirement as the customers are going to share the workbook with their suppliers to agree on a plan.

So I wrote a macro, which does the job perfectly, but it won't run on "user-facing" environment.

I red an article "The macros are bad" about appropriate use of macros, where was recommended not to use macros on a "user-facing" environment. In one of the comments there was a suggestion that one can use NPrinting for such tasks.

Please point out where I can read about implementing such functionality (with NPrinting or other tool).

With Microsoft BI stack it is easy to implement, but in the company I am currently working QlikView is preferred.

I don't wan't to tell my customers that they have to pay more for such a basic functionality.

Of course they manually can copy and paste the information in a excel template, but this is prone to mistakes and also is time consuming. The users might need to export hundreds of plans for different materials per day. If they can just change the selection and with a pres of a button save an excel file with date stamp and material as name (what macro does when it runs), that will make the solution to fly. Unfortunately I don't have the knowledge, how to do it without a macro.

I will highly appreciate your help.

Ross

[#2]

Thank you Petter.

The solution with PowerShell is quite involving and there is no guarantee that it is going to work. In the comments on article PSExcel: Excel automation without Excel – Rambling Cookie Monster<http://ramblingcookiemonster.github.io/PSExcel-Intro/> one can see various problems developers faced. I don’t have the luxury to play around, my clients need the solution implemented as it impacts their day to day work tremendously.

So I prefer the idea of using macro – your last suggestion. The problem is that macro works perfectly on my dev machine, but doesn’t run on server. I don’t administrate the server, so I can’t be sure that macros are allowed. Server admin confirmed that it is the case.

I will have a look at ServerSideExportEx reference.

What the current macro does is pretty simple: open an excel template, write the data in the relevant ranges and save it with name depending on the selection ( Recovery Plan for material & release number & date) in the local folder. The cells with calculations are not affected so the final file is as the user wanted it with logo, formatting and calculations left untouched. It is important to transfer the data of all three tables on the correct place in excel template. I am not sure that ServerSideExportEx can do that.

I think the problem might be related to saving the file in local folder.

Thank you for helping me to find a quick solution.

Ross

[petter]

Your macro might use Excel directly and that requires that Excel itself is installed on the server. What I tried to suggests that you have two Excel files that are connected. One with the data coming from QlikView that is the variant part and one that contains the formulas and client specific that use Excel external references to the first file. In this solution you will not need Excel installed on the QlikView Server.

[#4]

Thanks for keep helping

It might be also security issue. I wasn't precise. If I run macro on the server with my credentials it executes, but - doesn't with the user credentials. I have some admin rights, but it is possible that user doesn't have right to execute macro.

I will try your suggestion below.

Ross

Sent from: YOGA Tablet 2-1050F

[#5]

I went through the presentation and the samples but there is nothing which might help for my particular problem.

I have Barry's book, but there is also nothing related.

[#6]

I've been thinking about the suggestion with two files, but don't see how it will help.

What will make the data transferred from QlikView to Excel on the first place? Then how the data and template files will be connected if the data file doesn't exist initialy?

Actually it make thinks more complicated and don't have the functionality to safe end file with specific name convention.

I just have to find out how the users can run the current macro - that is it.

Or of course, if their licence (or security) doesn't allow running macros to find solution, which gives me the same functionality without macro.

[petter]

Well it might help you understand and others to understand that Macros aren't always bad...

[petter]

There is an option of using Microsoft Access 2013 Runtime. This includes and ODBC-driver for Excel which can be used to write to Excel files without the use of Excel at all. So you can create Excel XLSB (a binary variant of XLSX-file). This file can be an already existing XLSB-file that contain formulaes and fomatting. You can then fill in text and numbers in the appropriate places with a simplified SQL dialect.

[#9]

The question is how? What I have to use on a QlikView site if it is not a macro?

I know there many ways to achieve such functionality.

For example with Microsoft BI you can create a cube and the users can do whatever self analysis they want in Excel by connecting to it.

But we are using QlikView here…

I need help how can I run a macro in QlikView analysis form Access point and make it work the same way as it works on desktop version.

1. What I need to do to achieve that?

2. If it is not possible, is there a QlikView way/tool to do it?