topic Re: Macro export to excel in Qlikview Server in QlikView

Macro export to excel in Qlikview Server

Anonymous — Fri, 26 Aug 2016 11:55:03 GMT

Hi folks.

I will ask a question which the answer i know, but just to make sure, let's go.

I created a vision to my prevention loss department and bacause complexity i did mutiples chart diferent and now they want export all chart to excel in once time in the same sheet....OK...I did this using Macro...Like this:

Sub Exportar_Objetos_Excel

  Set XLApp = CreateObject("Excel.Application")
  Set XLDoc = XLApp.Workbooks.Add
  Set XLSheet = XLDoc.Worksheets(1)
  XLApp.Visible = True

  XLApp.Cells.Select
     XLApp.ActiveWindow.DisplayGridlines = False
  XLApp.Range("A5").Select

  ActiveDocument.GetSheetObject("CH01").CopyBitMapToClipboard
  XLSheet.Paste

  XLSheet.Range("B25").Select
  ActiveDocument.GetSheetObject("CH02").CopyTableToClipboard True
  XLSheet.Paste

End Sub

Its work...buuut only in Qlikiview Desktop, with Office installed...But the user use qlikview in browser, you know, There were not excel installed in my server, so my Macro not worked...=/
Buuuuuut, in Qlikview server there is a option "SEND TO EXCEL", and this f**** work in browser, why?

Summing up, i nedd create a button in qlikview, with macro which does the same "send to excel", but send mutiples chart once time, but ot usign "CreateObject("Excel.Application")" , because this not work on server...

If not exist solution, I understand...but if someone has passed to a situation similar, and resolved...coment!!!

I'll be very thankfull.

Thanks for your time.

"sorry by my english, i'm training XD"

Re: Macro export to excel in Qlikview Server

avinashelite — Fri, 26 Aug 2016 13:02:32 GMT

have you enabled the Allow system access in the server

Try like this

In access point

Ctrl+Shift+M> Click on Enable System access

Re: Macro export to excel in Qlikview Server

Anonymous — Fri, 26 Aug 2016 15:11:15 GMT

Did not work...=/

My Macro script

Does not exist a expression in vbscript which say to pc look to local machine not to server?

Look bellow, this is in browser:

After this, a excel save in c:\user\download and finish...

I want this, but create a Macro, for send all chart once time...

Re: Macro export to excel in Qlikview Server

Fri, 26 Aug 2016 18:22:33 GMT

The 'Set XLApp = CreateObject("Excel.Application")' is actually opening up an instance of Excel. In fact, you should have seen Excel open up on your desktop when you ran it. Especially since you have the 'XLApp.Visible = True' in your code above. Since Excel is not installed on your server this is why it is not working, as the macro is trying to find and run the Excel application.

The only other option to export to Excel in a macro is to do similar to the following:

Sub ExportExcel

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ExportBiff "C:\CH01.xls"

End sub

However, unfortunately this will not allow you to export multiple charts to the same Excel workbook.

Since your requirement is to have multiple charts in the same Excel, the only option I see for you is to have Excel installed on your server. Then take a look at the following link for a great example of exporting multiple objects to the same Excel workbook: http://www.qlikblog.at/971/qliktip-32-exporting-multiple-objects-single-excel-document/

Hope this is helpful in confirming your suspicions.

- Stan

Re: Macro export to excel in Qlikview Server

santiago_respane — Fri, 26 Aug 2016 18:38:06 GMT

Hi,

besides Avinash advice that is a must do in order for you macro to work, here's my version of the macro for exporting.

The idea is to have one macro for all charts in the report.

Macro code:

sub ExportChart

set MyTable = ActiveDocument.GetSheetObject(ActiveDocument.Variables("v.ChartToExport").GetContent().String)

Const xlCenter = -4108

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = True

set XLDoc = XLApp.Workbooks.Add

'Some title setting

XLDoc.Sheets(1).name = MyTable.GetCaption.Name.v

set XLSheet = XLDoc.Worksheets(1)

'Some sheet formatting

set XLSheet = XLDoc.Worksheets(1)

XLSheet.Columns.Interior.color = RGB(255,255,255)

'Copy table

Mytable.CopyTableToClipboard true

XLSheet.Paste XLSheet.Range("A1")

XLSheet.Columns("A:Z").EntireColumn.AutoFit

XLSheet.Columns("A:Z").VerticalAlignment = xlCenter

End Sub

In your case for exporting multiple charts, you can add a button, you add two actions in pairs for every object to export, as follows:

Object One
1. External -> Set Variable -> v.ChartToExport = MyTableID1
2. External -> Run MAcro -> ExportChart
Object Two
1. External -> Set Variable -> v.ChartToExport = MyTableID2
2. External -> Run MAcro -> ExportChart
Object 3

This will generate one xls file for each object.

Please let me know if this helps.

Kind regards,

Re: Macro export to excel in Qlikview Server

Fri, 26 Aug 2016 19:24:59 GMT

This will, of course, only work if Excel is installed on the machine where the marco is run.

Re: Macro export to excel in Qlikview Server

Anonymous — Mon, 29 Aug 2016 12:10:04 GMT

Thank you guys, but as Stan said, don't have any way to do that, if the Excel not installed in the server, unfortunately.

Only way to do that is export do .txt or .csv.

Thank you Avinash, Santiago and Stan...I tested all code, and all worked, but to qlikview desktop,like i said.

But thanks anyway...Until next time.

Re: Macro export to excel in Qlikview Server

santiago_respane — Mon, 29 Aug 2016 12:22:40 GMT

You had already specified that in your post, and as you said, OF COURSE it will only work if excel is installed in client machine.

Thanks anyway for your reply.