Macro export to excel in Qlikview Server

[Anonymous]

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"

[avinashelite]

have you enabled the Allow system access in the server

Try like this

In access point

Ctrl+Shift+M> Click on Enable System access

[Anonymous]

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

[#4 · Accepted solution]

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

[santiago_respane]

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:

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

This will generate one xls file for each object.

Please let me know if this helps.

Kind regards,

[#6]

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

[Anonymous]

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.

[santiago_respane]

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.