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

[petter]

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.

[petter]

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

[#4]

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

[petter]

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

A)

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

[#6]

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

[petter]

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

[#8]

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

Sent from: YOGA Tablet 2-1050F

[petter]

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.

[petter]

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