topic How to create Excel reports ? in QlikView

How to create Excel reports ?

Mon, 01 Mar 2010 17:06:01 GMT

Hi all !

First, excuse me for my english. Foreing languages isn't a great capacity for me.

My question is quite easy :

How can I export tables and graph to Excel ?

Actualy, a pdf report isn't what I want. And the only way to create an report on Excel, with design and potentialy dynamic (ex. : one table for each value of a choosen dimension), is to use vbscript macro.

If this way can resolve my problems, it's not easy for a non-developper client who want to create his report himself.

And I suffered some errors when I export table with CopyTabletoClipboard. Sometimes, a table does'nt accept to copy. It works with a selection, another one, another one, etc... and bug when it wants.

I read and find some software to use. There's Nprinting or Ireport (Jasperreports). But both are not solving what I want.

Which over way can I take ?

Thanks all for your answer !

How to create Excel reports ?

vgutkovsky — Mon, 01 Mar 2010 17:17:56 GMT

You can enable an Export to Excel icon to appear in the caption bar. See the following screenshots for an illustration:

-----------------------------------------------------------------

Hope this helped.

How to create Excel reports ?

Mon, 01 Mar 2010 17:28:13 GMT

I know this but it will only export the selected table. My question is about a real report, like what we find in the Report menu of QV.

Creating an Excel file with 1 or more table/graphe, dynamics with a dimension...

How to create Excel reports ?

vgutkovsky — Mon, 01 Mar 2010 18:06:47 GMT

If you just want an image, you can use the CopyBitmapToClipboard function in a macro. You can do this for the whole sheet (ActiveDocument.ActiveSheet.CopyBitmapToClipboard) or for an object (ActiveDocument.GetSheetObject ("ID").CopyBitmapToClipboard). The code might look something like this:


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
ActiveDocument.GetSheetObject("CH01").CopyBitmapToClipboard
objExcel.ActiveSheet.Paste

You would then associate this with a button so users can generate the "reports."

Regards,

How to create Excel reports ?

Mon, 01 Mar 2010 18:19:10 GMT

Thanks, but this is always not the things I want to do.

If I want to export to Excel, it's to put the data from tables into cells. So I use the CopyTableToClipboard.

How to create Excel reports ?

vgutkovsky — Mon, 01 Mar 2010 19:08:44 GMT

So you're saying that you want a combinational export, like copying the object (versus text or image)? You can do this by right-clicking on the object and selecting Copy to Clipboard --> Object. Unfortunately, I'm not sure how to do this using macros. There are routines for copying tables and for copying images, but I don't know the proper function for copying an object itself. Hopefully someone else can contribute on this post...

Good luck,

How to create Excel reports ?

Tue, 02 Mar 2010 09:58:21 GMT

I already do this (copying tables or graph directly into excel with the CopyTableToClipboard macro command). If I could do what I want, sometimes, a table didn't succes the copy. And I don't know why !

That's why I'm searching for a software for creating excel report with data from QV. I see Ireport (Jasperreport) or Nprinting, but this is not really what I want.

So, I'm searching for another solution, but I don't know which one...

How to create Excel reports ?

chrisbrain — Tue, 02 Mar 2010 10:25:12 GMT

Hi,

You could try the beta of the Excel Connector we are developing (if you have not already seen it):

http://www.qlikster.com/post/QlikView-Excel-Connector-Beta-4-(New-Docs-and-QlikView-Expressions-Now-Supported!).aspx

This allows you to:

- Use a new Excel function to run any QlikView expression in a cell.
- Map the data from an QlikView table or chart (or text box) into an Excel cell range.

And therefore build reports in Excel driven by QlikView data.

It would be good to speak with you in a little more detail about what features you need as we are currently developing an updated version of the above. I'll ping you directly through this site.

How to create Excel reports ?

Tue, 02 Mar 2010 12:10:01 GMT

For example concerning my problem to export into excel, let's have a look on this joining qvw. When I want to copy manually this table to excel, I made a right click, then "Copy data to clipboard / Complet table". I open Excel and paste the clipboard into. Excel bug and i should kill his process to close it.

But, if I change my selection "Secteur" from "1501 ANNECY" to "1904 PAU-TARBES" and redo the same action (copy to clipboard, open excel and paste), it works !

Do you have the same problem ? My configuration is Normal 0 21 false false false FR X-NONE X-NONE MicrosoftInternetExplorer4

Windows XP Pro SP3

Office 2007

QlikView 9.00.7320.7 SR2

Until i have no solution for this bug, the export data to excel with macro isn't a good way for me.

How to create Excel reports ?

Wed, 03 Mar 2010 11:17:49 GMT

Hi,

I don't know what is wrong but this Excel connector isn't working with QV9 (SR2) in Excel 2007. Everything is ok with license, but when I drop qv object to excel and press "Refresh Links" I get the "Error: Interop.Excel" message.

It says:

" What happened:
AddinExpress.MSO.ADXRibbonButton error: the add-in has fired an exception.
Where presumably this happened:
An error occured in the code of the QlikViewExcelConnector add-in. See 'Details' section to get more detailed information about the error."

Maybe someone has any idea what's wrong and can help my to solve the problem.

Best regards,

Milda

How to create Excel reports ?

Wed, 03 Mar 2010 14:36:58 GMT

Heddicmi,

What level of detail are you looking for in your export?

If you post the type of report you want or contact our support team at support [at] nprinting.com, I'm sure we can come up with something that will solve your problem,

In the mean time, you can download a sample template created with your data: The first worksheet has the data in a straight table and the second worksheet has the data in a pivot table with a graphic.

Just install NPrinting if you haven't already (it's completely free www.nprinting.com), open the .qvw which you posted to the forum and select your analysis. Then select the template I created and click on Excel Export. This will open an Excel file populated with the data from your .qvw.

If you modify the template you can add or remove fields, add or change graphics etcetera.

Hope this helps

Best

The NPrinting Support Team

How to create Excel reports ?

Wed, 03 Mar 2010 14:52:18 GMT

Thanks, but there's no file attached to your post ;o)

Concerning NPrinting, the only problem is :

I have the sensation that I need to do the jobs twice. I create my qvw, table and graph. Then I use Nprinting for the insertion of datas into Excel. But for the graph, I need to create it onr more time with Excel.

How to create Excel reports ?

Wed, 03 Mar 2010 15:09:35 GMT

Sorry about that - just loaded the file onto my post.

On your second point - yes you do have to make your graphs in NPrinting, but once you've created the template, you can use it over and over and also save it with a new name to modify it as you wish.

Do you create analyses and graphics in QlikView or do you use QlikView to extract data and graphics from existing QlikView files to export them to Excel?

Regards

How to create Excel reports ?

jennyc — Wed, 03 Mar 2010 15:35:06 GMT

Hi!

I tried to use the macro you recommended but it doesn't work for me. When clicking the buttton it's highlighting the row "Set objExcel = CreateObject("Excel.Application")" with a comment that the activeX-component couldn't create the object: 'Excel-Application'. Am I doing something wrong?

//Jenny

How to create Excel reports ?

Thu, 04 Mar 2010 10:42:46 GMT

It's a security pb like in windows. When you are in the macro editing window, above the "ok" button, you have to selected the allowed option in the two list.

How to create Excel reports ?

jennyc — Thu, 04 Mar 2010 10:56:47 GMT

Now it works great. Thanks a lot!

//Jenny

How to create Excel reports ?

chrisbrain — Thu, 04 Mar 2010 11:18:43 GMT

Hi Milda,

I will contact you directly through the site to see if I can get some more details and help you resolve this issue. The Excel Connector is still a beta product but it has been tested with Excel 2007 and QlikView 9.

How to create Excel reports ?

Thu, 04 Mar 2010 12:00:31 GMT

Hi Chris,

As we had discussed earlier .it works in excel 2003 ..in excel 2007 the com-addins does not get loaded though you do it manually also.

rgds

tapas