topic Re: Export QlikView sheet to Excel workbook with formulas in QlikView

Export QlikView sheet to Excel workbook with formulas

Fri, 19 Feb 2016 14:26:48 GMT

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

Re: Export QlikView sheet to Excel workbook with formulas

petter — Fri, 19 Feb 2016 17:55:35 GMT

There is no functionality built in that can translate QlikView expressions or values into Excel formulas. They way you make logic in a QlikView application is completely different from how you make logic in formulas in cells in a spreadsheet.

Re: Export QlikView sheet to Excel workbook with formulas

petter — Fri, 19 Feb 2016 17:58:00 GMT

You could however have a simple Excel template containing all the calculations and formulas and having that referring to all the data in an exported Excel sheet - which doesn't contain an logic or formulas...

Re: Export QlikView sheet to Excel workbook with formulas

Fri, 19 Feb 2016 18:14:04 GMT

Hi Peter,

You don't tell me something new.

My macro does this perfectly, but I am asking for solution without macro.

My question is: How can l make the data be copied in the excel template with a pres of a button without macro.

If you can help with this that would be great.

Thank you.

Ross

Sent from: YOGA Tablet 2-1050F

Re: Export QlikView sheet to Excel workbook with formulas

petter — Fri, 19 Feb 2016 19:29:27 GMT

Maybe this could work for you - not involving a Macro - only Button and Action:

This will create a Tab-separated file which is unique for the specific user. It could store it to a user-specific folder instead of course. With a separate folder you could enforce rights that prevent users for looking at each others exported data.

B) Next you will have to use PowerShell to convert the Tab-separated file into Excel

This guy has a blog describing a library called PS-Excel that could do the job:

PSExcel: Excel automation without Excel – Rambling Cookie Monster

C) Lastly - to be able to trigger automatic conversion from Tab-separated file to Excel-file with PowerShell you will have to make use of Windows FileSystemWatcher that can also be set up with PowerShell. FileSystemWatcher is a built-in function in the Windows operating system that can be used to trigger programs to start as files are created or modified in a specific folder.

NB! This will only work smoothly with the IE-client - that is the save button without further questions/prompts. On the Ajax-client the button will cause a nasty pop-up asking the user where to store the export...

I hope this helps...

If you think this might be a route you would like to follow I could give you some skeleton application that proves the entire concept. Give me a hint ....

kind regards

Petter Skjolden

Re: Export QlikView sheet to Excel workbook with formulas

Fri, 19 Feb 2016 19:54:52 GMT

Thanks a million, Petter. I will have a look at the options you had mentioned. This is something l needed - QlikWiew kind of solution.

I am not sure if it is going to work, but it is a good starting point.

I will keep you informed .

Ross

Sent from: YOGA Tablet 2-1050F

Re: Export QlikView sheet to Excel workbook with formulas

petter — Fri, 19 Feb 2016 20:10:30 GMT

Actually I forgot to ask you ... are you going to use the Ajax-client or IE-plugin for the users of the application?

Re: Export QlikView sheet to Excel workbook with formulas

Fri, 19 Feb 2016 20:21:16 GMT

Yes that is the reason I can't use my solution with macro.

Sent from: YOGA Tablet 2-1050F

Re: Export QlikView sheet to Excel workbook with formulas

petter — Sat, 20 Feb 2016 07:58:42 GMT

It is important to know which one of the two clients your users are going to use. The choice does affect the kind of solution that can be made. They are not equivalent.

When it comes to macros they are not all bad. To make a point some people are very categorical and claim that "Macros are bad" - but that is not true in the sense that they are good for nothing.

Barry Harmsen has a good blog at qlikfix.com go his site and search the tag "macro" and then you will get a more balanced view on where macros are good, bad and ugly...

In your case I would make use of one macro QlikView object model function called:

ServerSideExportEx()

The nice thing is that it can export directly to an XLS-file and it works with both the IE-client and the Ajax-client on the server. As long as macro execution is allowed in your applications and on the server it will work ok. I have used it many times. Seriously I can't see why the use of this constitutes anything "bad".

It can be used like this:

set lb = ActiveDocument.GetSheetObject("LB01")
lb.ServerSideExportEx "D:\aFolderOnTheQlikViewServer\Export\xxxx\ChartXYZ.xls" , "" , 5
  ' 0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF
 ' BIFF is the old Excel format that has file extension XLS

So unless someone has set an absolute restriction on disallowing macros entirely then I will advice you to still use it.

Re: Export QlikView sheet to Excel workbook with formulas

petter — Sat, 20 Feb 2016 08:53:53 GMT

Here is Barry Harmsens first slide of his presentation that I referred to earlier:

Re: Export QlikView sheet to Excel workbook with formulas

Sat, 20 Feb 2016 09:52:29 GMT

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

Re: Export QlikView sheet to Excel workbook with formulas

petter — Sat, 20 Feb 2016 10:18:43 GMT

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.

Re: Export QlikView sheet to Excel workbook with formulas

Sat, 20 Feb 2016 11:10:30 GMT

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

Re: Export QlikView sheet to Excel workbook with formulas

Sat, 20 Feb 2016 14:06:04 GMT

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.

Re: Export QlikView sheet to Excel workbook with formulas

Sat, 20 Feb 2016 14:25:21 GMT

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.

Re: Export QlikView sheet to Excel workbook with formulas

petter — Sun, 21 Feb 2016 22:07:58 GMT

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

Re: Export QlikView sheet to Excel workbook with formulas

petter — Sun, 21 Feb 2016 22:10:39 GMT

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.

Re: Export QlikView sheet to Excel workbook with formulas

Mon, 22 Feb 2016 06:49:34 GMT

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?