topic Ajax 'send to excel' Button in QlikView

Ajax 'send to excel' Button

Mon, 14 Nov 2011 22:07:49 GMT

Is it possible, within the ajax client, to create a button that will send a chart to excel? So far from my experience you can only create macros that send objects to excel with the IE Plugin.

It would seem possible because the button on top of the charts works fine, unfortuanitally I need this functionality from a button.

Ajax 'send to excel' Button

Mon, 14 Nov 2011 22:14:05 GMT

unfortunately*

Ajax 'send to excel' Button

Tue, 15 Nov 2011 08:12:36 GMT

Hi,

do you need to export the chart data into excel or the whole image of the chart?

Regards,

Daniel

Re: Ajax 'send to excel' Button

Tue, 15 Nov 2011 18:16:29 GMT

Hello Daniel,

Just the chart data would be fine. It is a pivot table.

Thanks,

Tyler

Ajax 'send to excel' Button

Wed, 16 Nov 2011 08:00:42 GMT

Hi Tyler,

in AJAX most of the macro functionalities doesn't work. I had the same problem and I solved it by exporting first the chart data via macro to a txt file. Then, by a Shell Script, you can open an excel file with a macro inside which automatically fetches the data of the txt file and fills the excel file with it.

I know, it sounds a bit complicated, but it's the only workaround, I found.

Exporting the data of any chart to a txt file is simple:

Sub ExportChart()

path = "D:\Export1.txt" ' Set your path, where the txt file shall be created

Set chart1 = ActiveDocument.GetSheetObject("CH01") 'CH01 has to be the ID of your chart

chart1.Export path, ";"

' Open an Excel File via Shell

cmd = "D:\ExcelFileWithMacro.xls"

Set WSH = CreateObject("WScript.Shell")

WSH.Run (cmd),0

End Sub

Let me know if this helps you.

Regards,

Daniel

Re: Ajax 'send to excel' Button

Wed, 16 Nov 2011 18:43:52 GMT

Hey Daniel,

This is very helpful and I think I am moving in the right direction. I couldn't get the shell script to work but this export can create .csv files, which is nice. Here is the macro I am running now:

Sub ExportChart

path = "C:\Export1.csv" ' Set your path, where the txt file shall be created

Set chart1 = ActiveDocument.GetSheetObject("CH01") 'CH01 has to be the ID of your chart

chart1.Export path, ","

End Sub

Do you know of a way to prompt the opening of this Export1.csv file? I tried a 'Launch' action but that only works in the developer not from the ajax client. Launch seems to just open a new tab when run from the ajax client.

Thanks,

Tyler

Ajax 'send to excel' Button

Thu, 17 Nov 2011 07:19:09 GMT

Hi Tyler,

the shell script works, when you set the security levels on the left side of the macro editor to "System Access", not the "safe mode".

Opening the file for the user means download it via an URL, because otherwise the file is only opened on the server which the user can't see.

So I'm suggesting using a fileserver (if you have no existing one, you can try the HFS (HTTP File Server), which is a freeware tool and don't need any installations). Then you can make a new button with a "Open URL" action in it which opens (download) the file. Then the user can see it.

The problem, why you shouldn't put the Open URL action into the button which starts the export Macro is that the actions don't wait til the macro finished. They are immediately triggered, after the macro launched. And because the macro takes a millisecond longer, the Open URL Action won't fetch the new exported file.

Hope this helps you.

Regards,

Daniel

Re: Ajax 'send to excel' Button

pgdavis2 — Tue, 03 Apr 2012 18:26:28 GMT

For some reason I can't use the above code to export to csv in ajax. Security is set to System Access...what other settings are needed?

Thanks

Paul

Re: Ajax 'send to excel' Button

Brett_Bleess — Tue, 03 Apr 2012 22:29:42 GMT

Have you looked at the ServerSideExportEx API in the API Guide? I believe this is what you will need in order to use the button in Ajax client.

Re: Ajax 'send to excel' Button

pgdavis2 — Wed, 04 Apr 2012 01:11:12 GMT

I think you're right. Unfortunately there seems to be a bug in QV11 where only xml export works using this method. I did some research and found someone else reported this also:

http://community.qlik.com/message/190250#190250

Thanks for the help!

Re: Ajax 'send to excel' Button

Brett_Bleess — Wed, 04 Apr 2012 16:20:21 GMT

Correct the ID is 45241, but you can try the html format instead:

sub HTML
set tb = ActiveDocument.GetSheetObject("TB01")
tb.ServerSideExportEX "C:\test.html" , ";" , 0
end sub

Re: Ajax 'send to excel' Button

Brett_Bleess — Wed, 04 Apr 2012 16:39:29 GMT

The only problem with the above is the code/process to get the file from the server to the client, which you will need to write. Regarding things in IE that can block things, the Enable Protected Mode setting is most off the culprit in these cases, so you can be sure your QVServer site is in Local Intranet or Trusted Sites Security Zones, as those should have it disabled by default. Other than that, I would recommend you use Fiddler to trace things, as that will likely give you a better indication as to where things are getting stuck, or you can simply try the F12 Developer Tools option in IE as well, as that may work as well.